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

2007-12-24 02:50:24 121 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

统计报表 -- sql统计语句需要数据统计页面，肯定需要导出数据，于是，边学边写，完成了一段sql~ 最早的版本是这样的：分三条sql查出三种不同的状态的记录数(总记录，未支付，已支付) SELECT COUNT(*) AS RecordCount FROM t_record WHERE CreateTime &gt;= '2019-01-01 00:00:00' AND CreateTime &lt; '2019-0... 阅读详情

求一條 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
請朋友幫助！謝謝！

...全文

【SQL Server】统计信息实战+实用SQL语句本文重点介绍实际统计信息的运用，包括如何查询统计信息，统计信息相关的重要指标，如何找到系统中需要更新的统计信息，以及统计信息语句监控SQL语句运行情况等阅读详情

molan68

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

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

相关推荐