(SQL语句)按指定时间段分组统计

我现在有一张表：
  列名1                时间
03174190188 2009-11-01 07:17:39.217
015224486575 2009-11-01 08:01:17.153
013593006926 2009-11-12 08:04:46.560
013599584239 2009-11-22 08:53:27.763
013911693526 2009-11-23 08:53:51.683
013846472440 2009-11-23 08:54:57.233
013990353697 2009-11-24 08:55:25.077
013990353697 2009-11-25 08:56:01.327
013945594843 2009-11-26 08:57:02.233
013990353697 2009-11-27 08:57:29.700
013916597421 2009-11-28 08:59:49.390
03916995857 2009-11-29 09:11:05.607
015097712001 2009-11-30 09:13:50.293

现在想要做一个报表：

时段     2009-11-1 2009-11-2  2009-11-3  合计
00:00-01:00 0 0 0        0
01:00-02:00 0 0 0        0
02:00-03:00 0 0 0        0
03:00-04:00 0 0 0        0
04:00-05:00 0 0 0        0
05:00-06:00 0 0 0        0
06:00-07:00 0 1 1        2
07:00-08:00 1 4 4        9
08:00-09:00 11 16 13        .
09:00-10:00 11 26 13        .
10:00-11:00 12 29 25        .
11:00-12:00 6 7 11        .
12:00-13:00 4 9 2
13:00-14:00 5 10 11
14:00-15:00 13 16 23
15:00-16:00 14 5 17
16:00-17:00 10 5 18
17:00-18:00 7 3 6
18:00-19:00 7 2 5
19:00-20:00 4 0 5
20:00-21:00 5 3 0
21:00-22:00 2 0 0
22:00-23:00 2 1 0
23:00-24:00 0 0 0
合计         114      137      154      405

 用户输入一个时间段 ，比如开始时间 2009-12-1 结束时间 2009-12-30 这样。

方法1:

 

代码

-- 参考这个
-- > 测试数据：@table
declare   @table   table ( [ id ]   int , [ day ]   varchar ( 10 ), [ starttime ]   varchar ( 10 ), [ overtime ]   varchar ( 10 ), [ name ]   varchar ( 10 ))
insert   @table
select   1 , ' 20091202 ' ,  ' 09:00 ' , ' 16:00 ' , ' 张三 '


declare   @begdate   datetime , @enddate   datetime
select   @begdate   =   ' 20091129 ' , @enddate   =   ' 20091205 '

select  t. [ date ] ,t. [ time ] ,u. [ name ]   into  # temp   from
(
select   convert ( varchar ( 10 ), dateadd (hour, number , @begdate ), 112 )  as   [ date ] ,
convert ( varchar ( 10 ), dateadd (hour, number , @begdate ), 108 )  +   ' - '
+ convert ( varchar ( 10 ), dateadd (hour, number + 1 , @begdate ), 108 )  as   [ time ] ,
null   as   [ name ]
from  master.dbo.spt_values
where  type  =   ' P '  
and   dateadd (hour, number , @begdate )  <=   dateadd (hour, 18 , @enddate )
and   convert ( varchar ( 10 ), dateadd (hour, number , @begdate ), 108 )  >=   ' 08:00 '
and   convert ( varchar ( 10 ), dateadd (hour, number , @enddate ), 108 )  <=   ' 18:00 '
) t  left   join  
(
select   convert ( varchar ( 10 ), dateadd (hour,r. number , @begdate ), 112 )  as   [ date ] ,
convert ( varchar ( 10 ), dateadd (hour, number , @begdate ), 108 )  +   ' - '
+ convert ( varchar ( 10 ), dateadd (hour, number + 1 , @begdate ), 108 )  as   [ time ] ,
h.name
from  master.dbo.spt_values r , @table  h 
where  type  =   ' P '  
and   convert ( varchar ( 10 ), dateadd (hour, number , @begdate ), 108 )  >=  h. [ starttime ]
and   convert ( varchar ( 10 ), dateadd (hour, number , @enddate ), 108 )  <=  h. [ overtime ]
and   convert ( varchar ( 10 ), dateadd (hour,r. number , @begdate ), 112 )  =  h. [ day ]
) u
on  t. [ date ]   =  u. [ date ]   and  t. [ time ]   =  u. [ time ]

-- select * from #temp

declare   @sql   varchar ( 8000 )
select   @sql   =   ''

select   @sql   =   @sql   +   ' ,max(case [date] when  ' + [ date ] + '  then name else null end) as [ ' + ltrim ( datename (weekday, [ date ] )) + ' ] '
from  ( select   distinct   [ date ]   from  # temp ) t

select   @sql   =   ' select [time]  ' +   @sql   +   '  from #temp group by [time] '

-- print @sql

exec ( @sql )

drop   table  # temp

 

 

方法2:

 

代码

-- -----------------------------------
--   Author : liangCK 梁爱兰
--   Comment: 小梁 爱 兰儿
--   Date   : 2010-01-02 16:47:10
-- -----------------------------------
 
-- > 生成测试数据: #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   ' 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 ' ;

DECLARE   @sql   varchar ( 8000 );
SET   @sql   =   '' ;
SELECT   @sql = @sql + ' ,SUM(CASE WHEN DATEDIFF(day,B.时间, '''
                       + CONVERT ( varchar ( 10 ), DATEADD ( day , number , @minDate ), 120 )
                       + ''' )=0 THEN 1 ELSE 0 END) AS [ '
                         + CONVERT ( varchar ( 10 ), DATEADD ( day , number , @minDate ), 120 ) + ' ] '
FROM  master.dbo.spt_values 
WHERE  type  =   ' P '   AND   DATEADD ( day , number , @minDate ) <= @maxDate ;

DECLARE   @cmd   nvarchar ( 4000 );
SET   @cmd   =  N '
SELECT ISNULL(A.时段, '' 合计 '' ) AS 时段 ' + @sql + ' ,
    COUNT(列名1) AS 合计
FROM(
    SELECT 时段=RIGHT(100+number,2)+ '' :00~ '' +RIGHT(100+number+1,2)+ '' :00 '' ,
        MinDate = RIGHT(100+number,2)+ '' :00:00 '' ,
        MaxDate = RIGHT(100+number+1,2)+ '' :00:00 ''
    FROM master.dbo.spt_values
    WHERE type =  '' P ''  AND number < 24
) AS A
    LEFT JOIN (SELECT * FROM #tb 
                WHERE 时间 BETWEEN @minDate AND @maxDate) AS B
ON CONVERT(varchar(8),B.时间,108) >= A.MinDate
    AND CONVERT(varchar(8),B.时间,108) < A.MaxDate
GROUP BY A.时段 WITH ROLLUP; '

EXEC  sp_executesql  @cmd ,N ' @minDate datetime,@maxDate datetime ' , @minDate , @maxDate ;


DROP   TABLE  #tb;

 

 

结果:

 

时段 2009 - 11 - 01 2009 - 11 - 02 2009 - 11 - 03 2009 - 11 - 04 2009 - 11 - 05 2009 - 11 - 06 2009 - 11 - 07 2009 - 11 - 08 2009 - 11 - 09 2009 - 11 - 10 2009 - 11 - 11 2009 - 11 - 12 2009 - 11 - 13 2009 - 11 - 14 2009 - 11 - 15 2009 - 11 - 16 2009 - 11 - 17 2009 - 11 - 18 2009 - 11 - 19 2009 - 11 - 20 2009 - 11 - 21 2009 - 11 - 22 2009 - 11 - 23 2009 - 11 - 24 2009 - 11 - 25 2009 - 11 - 26 2009 - 11 - 27 2009 - 11 - 28 2009 - 11 - 29 2009 - 11 - 30 2009 - 12 - 01 合计 00 : 00 ~ 01 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 01 : 00 ~ 02 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 02 : 00 ~ 03 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 03 : 00 ~ 04 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 04 : 00 ~ 05 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 05 : 00 ~ 06 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 06 : 00 ~ 07 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 07 : 00 ~ 08 : 00 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 08 : 00 ~ 09 : 00 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 2 1 1 1 1 1 0 0 0 10 09 : 00 ~ 10 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 2 10 : 00 ~ 11 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11 : 00 ~ 12 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 12 : 00 ~ 13 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 13 : 00 ~ 14 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 14 : 00 ~ 15 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 15 : 00 ~ 16 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 16 : 00 ~ 17 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 17 : 00 ~ 18 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 18 : 00 ~ 19 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 19 : 00 ~ 20 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 20 : 00 ~ 21 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 21 : 00 ~ 22 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 22 : 00 ~ 23 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 23 : 00 ~ 24 : 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 合计 2 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 2 1 1 1 1 1 1 1 0 13

 

 

代码

-- -----------------------------------
--   Author : liangCK 梁爱兰
--   Comment: 小梁 爱 兰儿
--   Date   : 2010-01-02 16:47:10
-- -----------------------------------
 
-- > 生成测试数据: #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   ' 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 ' ;

DECLARE   @sql   varchar ( 8000 );
SET   @sql   =   '' ;
SELECT   @sql = @sql + ' ,SUM(CASE WHEN CONVERT(varchar(8),时间,108) >= '''
                         +RIGHT ( 100 + number , 2 )
                         + ' :00:00 ''  AND CONVERT(varchar(8),时间,108) <  '''
                         +RIGHT ( 100 + number + 1 , 2 ) + ' :00:00 '' THEN 1 ELSE 0 END) AS [ '
                         +RIGHT ( 100 + number , 2 ) + ' :00- ' +RIGHT ( 100 + number + 1 , 2 ) + ' :00] '
FROM  master.dbo.spt_values 
WHERE  type  =   ' P '   AND   number   <   24 ;

DECLARE   @cmd   nvarchar ( 4000 );
SET   @cmd   =  N ' SELECT ISNULL(CONVERT(varchar(10),时间,120), '' 合计 '' ) AS 时段 ' + @sql + ' ,
    COUNT(列名1) AS 合计
FROM #tb 
WHERE 时间 BETWEEN @minDate AND @maxDate
GROUP BY CONVERT(varchar(10),时间,120) WITH ROLLUP; ' ;

EXEC  sp_executesql  @cmd ,N ' @minDate datetime,@maxDate datetime ' , @minDate , @maxDate ;


DROP   TABLE  #tb;

 

 

 

代码

-- -----------------------------------
--   Author : liangCK 梁爱兰
--   Comment: 小梁 爱 兰儿
--   Date   : 2010-01-02 16:47:10
-- -----------------------------------
 
-- > 生成测试数据: #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   ' 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   else   0   end )  as   ' 07:00~08:00 ' ,
sum ( case   when   convert ( char ( 8 ),时间, 108 )  between   ' 08:00 '   and   ' 09:00 '   then   1   else   0   end )  as   ' 08:00~09:00 ' , count (a.列名1)  as   ' sum '
from  #tb a  right   join  
master..spt_values b  on   datediff (d,时间, dateadd (d, number , @minDate ))  =   0
where   dateadd (d, number , @minDate )  <=   @maxDate   and  b.type  =   ' p '   and  b. number   >=   0
group   by   convert ( char ( 10 ), dateadd (d, number , @minDate ), 120 )
order   by   1


drop   table  #tb



( 13  行受影响)
            00 : 00 ~ 01 : 00   01 : 00 ~ 02 : 00   02 : 00 ~ 03 : 00   03 : 00 ~ 04 : 00   04 : 00 ~ 05 : 00   05 : 00 ~ 06 : 00   06 : 00 ~ 07 : 00   07 : 00 ~ 08 : 00   08 : 00 ~ 09 : 00   sum
-- -------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2009 - 11 - 01   0             0             0             0             0             0             0             1             1             2
2009 - 11 - 02   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 03   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 04   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 05   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 06   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 07   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 08   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 09   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 10   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 11   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 12   0             0             0             0             0             0             0             0             1             1
2009 - 11 - 13   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 14   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 15   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 16   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 17   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 18   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 19   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 20   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 21   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 22   0             0             0             0             0             0             0             0             1             1
2009 - 11 - 23   0             0             0             0             0             0             0             0             2             2
2009 - 11 - 24   0             0             0             0             0             0             0             0             1             1
2009 - 11 - 25   0             0             0             0             0             0             0             0             1             1
2009 - 11 - 26   0             0             0             0             0             0             0             0             1             1
2009 - 11 - 27   0             0             0             0             0             0             0             0             1             1
2009 - 11 - 28   0             0             0             0             0             0             0             0             1             1
2009 - 11 - 29   0             0             0             0             0             0             0             0             0             1
2009 - 11 - 30   0             0             0             0             0             0             0             0             0             1
2009 - 12 - 01   0             0             0             0             0             0             0             0             0             0
警告: 聚合或其他  SET  操作消除了空值。

( 31  行受影响)

代码

-- -----------------------------------
--   Author : liangCK 梁爱兰
--   Comment: 小梁 爱 兰儿
--   Date   : 2010-01-02 16:47:10
-- -----------------------------------
 
-- > 生成测试数据: #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   ' 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   isnull ( convert ( char ( 10 ), dateadd (d, number , @minDate ), 120 ), ' sum ' ),
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   else   0   end )  as   ' 07:00~08:00 ' ,
sum ( case   when   convert ( char ( 8 ),时间, 108 )  between   ' 08:00 '   and   ' 09:00 '   then   1   else   0   end )  as   ' 08:00~09:00 ' , count (a.列名1)  as   ' sum '
from  #tb a  right   join  
master..spt_values b  on   datediff (d,时间, dateadd (d, number , @minDate ))  =   0
where   dateadd (d, number , @minDate )  <=   @maxDate   and  b.type  =   ' p '   and  b. number   >=   0
group   by   convert ( char ( 10 ), dateadd (d, number , @minDate ), 120 )
with  rollup
order   by   1


drop   table  #tb


            00 : 00 ~ 01 : 00   01 : 00 ~ 02 : 00   02 : 00 ~ 03 : 00   03 : 00 ~ 04 : 00   04 : 00 ~ 05 : 00   05 : 00 ~ 06 : 00   06 : 00 ~ 07 : 00   07 : 00 ~ 08 : 00   08 : 00 ~ 09 : 00   sum
-- -------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2009 - 11 - 01   0             0             0             0             0             0             0             1             1             2
2009 - 11 - 02   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 03   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 04   0             0             0             0             0             0             0             0             0             0
2009 - 11 - 05   0             0             0             0             0             0             0             0             0             0
......
2009 - 11 - 28   0             0             0             0             0             0             0             0             1             1
2009 - 11 - 29   0             0             0             0             0             0             0             0             0             1
2009 - 11 - 30   0             0             0             0             0             0             0             0             0             1
2009 - 12 - 01   0             0             0             0             0             0             0             0             0             0
sum          0             0             0             0             0             0             0             1             10            13
 

( 32  行受影响)

 

 

转自:http://topic.csdn.net/u/20100102/16/bf7811f6-b79b-4221-9ee9-42ae0b8e1c6c.html

 

点击下载:用sql语句按指定时间段分组统计