sqlÖеķֶÎÁбí

±í£ºÓû§ºÅÂ룬µÇ¼ʱ¼ä
ÏÔʾ £ºÃ¿ÈյǼ¸÷ʱ¼ä¶ÎµÄµÇ¼ÈËÊý£¬ºÍÿÌìµÇ¼ÈËÊý
if isnull(object_id('#tb'),'')=''
drop table #tb
CREATE TABLE #tb(ÁÐÃû1 varchar(12),ʱ¼ä datetime)
INSERT INTO #tb
SELECT '03174190188','2009-11-01 07:17:39.217' UNION ALL
SELECT '015224486575','2009-11-01 08:01:17.153' UNION ALL
SELECT '013593006926','2009-11-12 08:04:46.560' UNION ALL
SELECT '013593006926','2009-11-12 07:05:46.560' UNION ALL
SELECT '013599584239','2009-11-22 08:53:27.763' UNION ALL
SELECT '013911693526','2009-11-23 08:53:51.683' UNION ALL
SELECT '013846472440','2009-11-23 08:54:57.233' UNION ALL
SELECT '013990353697','2009-11-24 08:55:25.077' UNION ALL
SELECT '013990353697','2009-11-25 08:56:01.327' UNION ALL
SELECT '013945594843','2009-11-26 08:57:02.233' UNION ALL
SELECT '013990353697','2009-11-27 08:57:29.700' UNION ALL
SELECT '013916597421','2009-11-28 08:59:49.390' UNION ALL
SELECT '03916995857','2009-11-29 09:11:05.607' UNION ALL
SELECT '015097712001','2009-11-30 09:13:50.293'
--SQL²éѯÈçÏÂ:
DECLARE @minDate datetime,@maxDate datetime;
SELECT @minDate = '2009-11-1',@maxDate = '2009-12-01';
select convert(char(10),dateadd(d,number,@minDate),120),
sum(case when convert(char(8),ʱ¼ä,108) between '00:00' and '01:00' then 1 else 0 end) as '00:00~01:00',
sum(case when convert(char(8),ʱ¼ä,108) between '01:00' and '02:00' then 1 else 0 end) as '01:00~02:00',
sum(case when convert(char(8),ʱ¼ä,108) between '02:00' and '03:00' then 1 else 0 end) as '02:00~03:00',
sum(case when convert(char(8),ʱ¼ä,108) between '03:00' and '04:00' then 1 else 0 end) as '03:00~04:00',
sum(case when convert(char(8),ʱ¼ä,108) between '04:00' and '05:00' then 1 else 0 end) as '04:00~05:00',
sum(case when convert(char(8),ʱ¼ä,108) between '05:00' and '06:00' then 1 else 0 end) as '05:00~06:00',
sum(case when convert(char(8),ʱ¼ä,108) between '06:00' and '07:00' then 1 else 0 end) as '06:00~07:00',
sum(case when convert(char(8),ʱ¼ä,108) between '07:00' and '08:00' then 1