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

molan68 2007-12-24 02:50:24

求一條 sql語句，見下表：
Stime jobCode
2007-12-20 18:00:02 e
2007-12-20 8:30:09 r
2007-12-20 6:44:11 f
2007-12-20 5:02:12 a
2007-12-20 2:50:22 c
2007-12-20 0:00:06 c
按時間劃分，每兩小時一節，0-2，2-4，4-6。。。22-0
統計得如何（每天12節顯示，jobCode有數據表示1，無表示0）：
節數有無數據
1 1
2 1
3 0
4 1
5 1
6 0
7 0
8 0
9 0
10 1
11 0
12 0
請朋友幫助！謝謝！

...全文

98 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

ljking0731 2007-12-24

打赏
举报

--建表

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

wzy_love_sly 2007-12-24

打赏
举报



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.節數

節數有無數據
1 1
2 1
3 1
4 1
5 0
6 0
7 0
8 0
9 1
10 0
11 0
12 1

-狙击手- 2007-12-24

打赏
举报





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 行）

*/

dawugui 2007-12-24

打赏
举报

--这个按你的要求显示成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 行受影响)

*/

molan68 2007-12-24

打赏
举报

兄弟們，我先看一會兒不會影響加分你們吧？！

dawugui 2007-12-24

打赏
举报

wzy_love_sly

兄弟，你结贴率高不？

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

哈哈.

dawugui 2007-12-24

打赏
举报

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

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 行受影响)

*/

dawugui 2007-12-24

打赏
举报

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 行受影响)

*/

molan68 2007-12-24

打赏
举报

問題OK，馬上結！待請教！

wzy_love_sly 2007-12-24

打赏
举报

呵呵，哥们怕了

-狙击手- 2007-12-24

打赏
举报

哈哈，LS吓坏了

molan68 2007-12-24

打赏
举报

高！

wzy_love_sly 2007-12-24

打赏
举报

兄弟，你结贴率高不？

dawugui 2007-12-24

打赏
举报

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

比如有一个表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 行）