求一個sql語句---統計！難，請高手幫助！

--建表

create table #TimeDistribute(Stime datetime,jobCode char(2))

insert into #TimeDistribute

select '2007-12-20 18:00:02','e'

union all select '2007-12-20 8:30:09','r'

union all select '2007-12-20 6:44:11','f'

union all select '2007-12-20 5:02:12','a'

union all select '2007-12-20 2:50:22','c'

union all select '2007-12-20 0:00:06','c'

--生成完整的部分

declare @part int 

declare @sql varchar(1000)

set @sql='select ''0-2'' as part,1 as section '

set @part=2

while @part<24

begin

  set @sql=@sql+'union all select '''+rtrim(cast(@part as char(2)))+'-'+rtrim(cast(@part+2 as char(2)))+''','+cast(@part/2+1 as char(2))+' '

  set @part=@part+2

end

set @sql='select part,section , sum(case when (datepart(hour,stime)>=section) and (datepart(hour,stime)<=section+1) then 1 else 0 end ) as counts from #TimeDistribute,('+@sql+') as b group by part,section '



exec(@sql)

--结果

part  section     counts      

----- ----------- ----------- 

0-2   1           1

2-4   2           1

4-6   3           0

6-8   4           1

8-10  5           2

10-12 6           1

12-14 7           1

14-16 8           1

16-18 9           0

18-20 10          0

20-22 11          0

22-24 12          0

declare @tb table (Stime datetime,jobcode varchar(10))

insert into @tb select '2007-12-20 18:00:02','e'

insert into @tb select '2007-12-20 8:30:09','r'

insert into @tb select '2007-12-20 6:44:11','f'

insert into @tb select '2007-12-20 5:02:12','a'

insert into @tb select '2007-12-20 2:50:22','c'

insert into @tb select '2007-12-20 0:00:06','c'



select a.節數, sum(case when stime is null then 0 else 1 end) as 有無數據  from (

select 1 as 節數 union all

select 2 as 節數 union all

select 3 as 節數 union all

select 4 as 節數 union all

select 5 as 節數 union all

select 6 as 節數 union all

select 7 as 節數 union all

select 8 as 節數 union all

select 9 as 節數 union all

select 10 as 節數 union all

select 11 as 節數 union all

select 12 as 節數 

)a left join @tb b on a.節數=(case when datepart(hh,Stime)=0 then 24 else datepart(hh,Stime) end)*2/4

group by a.節數

declare @a table(id int, jobCode char(1), Stime smalldatetime)

insert @a select 1, 'e', '2007-12-20 18:00:02'

union all select 2, 'r', '2007-12-20 8:30:09'

union all select 3, 'f', '2007-12-20 6:44:11'

union all select 4, 'a', '2007-12-20 5:02:12'

union all select 5, 'c', '2007-12-20 2:50:22'

union all select 6, 'c', '2007-12-20 0:00:06'



select top 12 identity(int,0,2) as a,0 as b into # from sysobjects

update # set b = a+2



select right(100+a,2)+':00-'+right(100+b,2)+':00',

sum(case when datepart(hour,Stime) >=a and datepart(hour,Stime)<b then 1 else 0 end) 

from @a a ,

# aa

group by right(100+a,2)+':00-'+right(100+b,2)+':00'



drop table #

/*

                            

--------------- ----------- 

00:00-02:00     1

02:00-04:00     1

04:00-06:00     1

06:00-08:00     1

08:00-10:00     1

10:00-12:00     0

12:00-14:00     0

14:00-16:00     0

16:00-18:00     0

18:00-20:00     1

20:00-22:00     0

22:00-24:00     0



（所影响的行数为 12 行）

*/

--这个按你的要求显示成1,2,3,4...12

create table tb(Stime datetime, jobCode varchar(10))

insert into tb values('2007-12-20 18:00:02', 'e') 

insert into tb values('2007-12-20 8:30:09' , 'r') 

insert into tb values('2007-12-20 6:44:11' , 'f') 

insert into tb values('2007-12-20 5:02:12' , 'a') 

insert into tb values('2007-12-20 2:50:22' , 'c') 

insert into tb values('2007-12-20 0:00:06' , 'c')

go

select 时间段 = aa.a/2 + 1,

数量 = sum(case when datepart(hour,Stime) >=a and datepart(hour,Stime)<b then 1 else 0 end) 

from tb a ,

(select 0 a,2 b 

union all select  2,4

union all select  4,6

union all select  6,8

union all select  8,10

union all select  10,12

union all select  12,14

union all select  14,16

union all select  16,18

union all select  18,20

union all select  20,22

union all select  22,24

)aa

group by aa.a/2 + 1



drop table tb



/*

时间段         数量

----------- -----------

1           1

2           1

3           1

4           1

5           1

6           0

7           0

8           0

9           0

10          1

11          0

12          0



(12 行受影响)

*/

--上面漏了几个时间段,不好意思.

create table tb(Stime datetime, jobCode varchar(10))

insert into tb values('2007-12-20 18:00:02', 'e') 

insert into tb values('2007-12-20 8:30:09' , 'r') 

insert into tb values('2007-12-20 6:44:11' , 'f') 

insert into tb values('2007-12-20 5:02:12' , 'a') 

insert into tb values('2007-12-20 2:50:22' , 'c') 

insert into tb values('2007-12-20 0:00:06' , 'c')

go

select 时间段 = right(100+a,2)+':00-'+right(100+b,2)+':00',

数量 = sum(case when datepart(hour,Stime) >=a and datepart(hour,Stime)<b then 1 else 0 end) 

from tb a ,

(select 0 a,2 b 

union all select  2,4

union all select  4,6

union all select  6,8

union all select  8,10

union all select  10,12

union all select  12,14

union all select  14,16

union all select  16,18

union all select  18,20

union all select  20,22

union all select  22,24

)aa

group by right(100+a,2)+':00-'+right(100+b,2)+':00'



drop table tb



/*

时间段             数量

--------------- -----------

00:00-02:00     1

02:00-04:00     1

04:00-06:00     1

06:00-08:00     1

08:00-10:00     1

10:00-12:00     0

12:00-14:00     0

14:00-16:00     0

16:00-18:00     0

18:00-20:00     1

20:00-22:00     0

22:00-24:00     0



(12 行受影响)

*/

create table tb(Stime datetime, jobCode varchar(10))

insert into tb values('2007-12-20 18:00:02', 'e') 

insert into tb values('2007-12-20 8:30:09' , 'r') 

insert into tb values('2007-12-20 6:44:11' , 'f') 

insert into tb values('2007-12-20 5:02:12' , 'a') 

insert into tb values('2007-12-20 2:50:22' , 'c') 

insert into tb values('2007-12-20 0:00:06' , 'c')

go

select 时间段 = right(100+a,2)+':00-'+right(100+b,2)+':00',

数量 = sum(case when datepart(hour,Stime) >=a and datepart(hour,Stime)<b then 1 else 0 end) 

from tb a ,

(select 0 a,2 b 

union all select  2,4

union all select  6,8

union all select  10,12

union all select  14,16

union all select  16,18

union all select  18,20

union all select  20,22

union all select  22,24

)aa

group by right(100+a,2)+':00-'+right(100+b,2)+':00'



drop table tb



/*

时间段             数量

--------------- -----------

00:00-02:00     1

02:00-04:00     1

06:00-08:00     1

10:00-12:00     0

14:00-16:00     0

16:00-18:00     0

18:00-20:00     1

20:00-22:00     0

22:00-24:00     0



(9 行受影响)

*/

--这个按没小时统计,差不多.

比如有一个表timeTable,有字段id,score,inputDate.

数据如下

  id       score    inputDate

  1        2         '2007-4-5 3:33:33'

  2        1         '2007-4-5 4:33:33'

  3        4         '2007-3-5 3:33:33'

  4        2         '2007-4-2 2:33:33'

我要按时间分组统计score，结果如下

0:00-1:00   0

1:00-2:00   0

2:00-3:00   2

3:00-4:00   6

4:00-5:00   1

5:00-6:00   0

....

23:00-24:00 0



declare @a table(id int, score int, inputDate smalldatetime)

insert @a select 1, 2, '2007-4-5 3:33:33'

union all select 2, 1, '2007-4-5 4:33:33'

union all select 3, 4, '2007-3-5 3:33:33'

union all select 4, 2, '2007-4-2 2:33:33'



select right(100+a,2)+':00-'+right(100+b,2)+':00',

sum(case when datepart(hour,inputdate) >=a and datepart(hour,inputdate)<b then score else 0 end) 

from @a a ,

(select 0 a,1 b 

union all select  1,2

union all select  2,3

union all select  3,4

union all select  4,5

union all select  5,6

union all select  6,7

union all select  7,8

union all select  8,9

union all select  9,10

union all select  10,11

union all select  11,12

union all select  12,13

union all select  13,14

union all select  14,15

union all select  15,16

union all select  16,17

union all select  17,18

union all select  18,19

union all select  19,20

union all select  20,21

union all select  21,22

union all select  22,23

union all select  23,24

)aa

group by right(100+a,2)+':00-'+right(100+b,2)+':00'







--------------- ----------- 

00:00-01:00     0

01:00-02:00     0

02:00-03:00     2

03:00-04:00     6

04:00-05:00     1

05:00-06:00     0

06:00-07:00     0

07:00-08:00     0

08:00-09:00     0

09:00-10:00     0

10:00-11:00     0

11:00-12:00     0

12:00-13:00     0

13:00-14:00     0

14:00-15:00     0

15:00-16:00     0

16:00-17:00     0

17:00-18:00     0

18:00-19:00     0

19:00-20:00     0

20:00-21:00     0

21:00-22:00     0

22:00-23:00     0

23:00-24:00     0



（所影响的行数为 24 行）