300字范文 > (SQL语句)按指定时间段分组统计

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

时间：2019-12-18 10:22:16

相关推荐

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

我现在有一张表：

列名1 时间

03174190188 -11-01 07:17:39.217

015224486575 -11-01 08:01:17.153

013593006926 -11-12 08:04:46.560

013599584239 -11-22 08:53:27.763

013911693526 -11-23 08:53:51.683

013846472440 -11-23 08:54:57.233

013990353697 -11-24 08:55:25.077

013990353697 -11-25 08:56:01.327

013945594843 -11-26 08:57:02.233

013990353697 -11-27 08:57:29.700

013916597421 -11-28 08:59:49.390

03916995857 -11-29 09:11:05.607

015097712001 -11-30 09:13:50.293

现在想要做一个报表：

时段 -11-1 -11-2 -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

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

方法1:

代码 --参考这个

-->测试数据：@table

declare@tabletable([id]int,[day]varchar(10),[starttime]varchar(10),[overtime]varchar(10),[name]varchar(10))

insert@table

select1,'1202','09:00','16:00','张三'

declare@begdatedatetime,@enddatedatetime

select@begdate='1129',@enddate='1205'

selectt.[date],t.[time],u.[name]into#tempfrom

(

selectconvert(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],

nullas[name]

frommaster.dbo.spt_values

wheretype='P'

anddateadd(hour,number,@begdate)<=dateadd(hour,18,@enddate)

andconvert(varchar(10),dateadd(hour,number,@begdate),108)>='08:00'

andconvert(varchar(10),dateadd(hour,number,@enddate),108)<='18:00'

)tleftjoin

(

selectconvert(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

frommaster.dbo.spt_valuesr,@tableh

wheretype='P'

andconvert(varchar(10),dateadd(hour,number,@begdate),108)>=h.[starttime]

andconvert(varchar(10),dateadd(hour,number,@enddate),108)<=h.[overtime]

andconvert(varchar(10),dateadd(hour,r.number,@begdate),112)=h.[day]

ont.[date]=u.[date]andt.[time]=u.[time]

--select*from#temp

declare@sqlvarchar(8000)

select@sql=''

select@sql=@sql+',max(case[date]when'+[date]+'thennameelsenullend)as['+ltrim(datename(weekday,[date]))+']'

from(selectdistinct[date]from#temp)t

select@sql='select[time]'+@sql+'from#tempgroupby[time]'

--print@sql

exec(@sql)

droptable#temp

方法2:

代码 -------------------------------------

--Author:liangCK梁爱兰

--Comment:小梁爱兰儿

--Date:-01-0216:47:10

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

-->生成测试数据:#tb

CREATETABLE#tb(列名1varchar(12),时间datetime)

INSERTINTO#tb

SELECT'03174190188','-11-0107:17:39.217'UNIONALL

SELECT'015224486575','-11-0108:01:17.153'UNIONALL

SELECT'013593006926','-11-1208:04:46.560'UNIONALL

SELECT'013599584239','-11-2208:53:27.763'UNIONALL

SELECT'013911693526','-11-2308:53:51.683'UNIONALL

SELECT'013846472440','-11-2308:54:57.233'UNIONALL

SELECT'013990353697','-11-2408:55:25.077'UNIONALL

SELECT'013990353697','-11-2508:56:01.327'UNIONALL

SELECT'013945594843','-11-2608:57:02.233'UNIONALL

SELECT'013990353697','-11-2708:57:29.700'UNIONALL

SELECT'013916597421','-11-2808:59:49.390'UNIONALL

SELECT'03916995857','-11-2909:11:05.607'UNIONALL

SELECT'015097712001','-11-3009:13:50.293'

--SQL查询如下:

DECLARE@minDatedatetime,@maxDatedatetime;

SELECT@minDate='-11-1',@maxDate='-12-01';

DECLARE@sqlvarchar(8000);

SET@sql='';

SELECT@sql=@sql+',SUM(CASEWHENDATEDIFF(day,B.时间,'''

+CONVERT(varchar(10),DATEADD(day,number,@minDate),120)

+''')=0THEN1ELSE0END)AS['

+CONVERT(varchar(10),DATEADD(day,number,@minDate),120)+']'

FROMmaster.dbo.spt_values

WHEREtype='P'ANDDATEADD(day,number,@minDate)<=@maxDate;

DECLARE@cmdnvarchar(4000);

SET@cmd=N'

SELECTISNULL(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''

FROMmaster.dbo.spt_values

WHEREtype=''P''ANDnumber<24

)ASA

LEFTJOIN(SELECT*FROM#tb

WHERE时间BETWEEN@minDateAND@maxDate)ASB

ONCONVERT(varchar(8),B.时间,108)>=A.MinDate

ANDCONVERT(varchar(8),B.时间,108)<A.MaxDate

GROUPBYA.时段WITHROLLUP;'

EXECsp_executesql@cmd,N'@minDatedatetime,@maxDatedatetime',@minDate,@maxDate;

DROPTABLE#tb;

结果:

代码 -------------------------------------

--Author:liangCK梁爱兰

--Comment:小梁爱兰儿

--Date:-01-0216:47:10

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

-->生成测试数据:#tb

CREATETABLE#tb(列名1varchar(12),时间datetime)

INSERTINTO#tb

SELECT'03174190188','-11-0107:17:39.217'UNIONALL

SELECT'015224486575','-11-0108:01:17.153'UNIONALL

SELECT'013593006926','-11-1208:04:46.560'UNIONALL

SELECT'013599584239','-11-2208:53:27.763'UNIONALL

SELECT'013911693526','-11-2308:53:51.683'UNIONALL

SELECT'013846472440','-11-2308:54:57.233'UNIONALL

SELECT'013990353697','-11-2408:55:25.077'UNIONALL

SELECT'013990353697','-11-2508:56:01.327'UNIONALL

SELECT'013945594843','-11-2608:57:02.233'UNIONALL

SELECT'013990353697','-11-2708:57:29.700'UNIONALL

SELECT'013916597421','-11-2808:59:49.390'UNIONALL

SELECT'03916995857','-11-2909:11:05.607'UNIONALL

SELECT'015097712001','-11-3009:13:50.293'

--SQL查询如下:

DECLARE@minDatedatetime,@maxDatedatetime;

SELECT@minDate='-11-1',@maxDate='-12-01';

DECLARE@sqlvarchar(8000);

SET@sql='';

SELECT@sql=@sql+',SUM(CASEWHENCONVERT(varchar(8),时间,108)>='''

+RIGHT(100+number,2)

+':00:00''ANDCONVERT(varchar(8),时间,108)<'''

+RIGHT(100+number+1,2)+':00:00''THEN1ELSE0END)AS['

+RIGHT(100+number,2)+':00-'+RIGHT(100+number+1,2)+':00]'

FROMmaster.dbo.spt_values

WHEREtype='P'ANDnumber<24;

DECLARE@cmdnvarchar(4000);

SET@cmd=N'SELECTISNULL(CONVERT(varchar(10),时间,120),''合计'')AS时段'+@sql+',

COUNT(列名1)AS合计

FROM#tb

WHERE时间BETWEEN@minDateAND@maxDate

GROUPBYCONVERT(varchar(10),时间,120)WITHROLLUP;';

EXECsp_executesql@cmd,N'@minDatedatetime,@maxDatedatetime',@minDate,@maxDate;

DROPTABLE#tb; 代码 -------------------------------------

--Author:liangCK梁爱兰

--Comment:小梁爱兰儿

--Date:-01-0216:47:10

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

-->生成测试数据:#tb

CREATETABLE#tb(列名1varchar(12),时间datetime)

INSERTINTO#tb

SELECT'03174190188','-11-0107:17:39.217'UNIONALL

SELECT'015224486575','-11-0108:01:17.153'UNIONALL

SELECT'013593006926','-11-1208:04:46.560'UNIONALL

SELECT'013599584239','-11-2208:53:27.763'UNIONALL

SELECT'013911693526','-11-2308:53:51.683'UNIONALL

SELECT'013846472440','-11-2308:54:57.233'UNIONALL

SELECT'013990353697','-11-2408:55:25.077'UNIONALL

SELECT'013990353697','-11-2508:56:01.327'UNIONALL

SELECT'013945594843','-11-2608:57:02.233'UNIONALL

SELECT'013990353697','-11-2708:57:29.700'UNIONALL

SELECT'013916597421','-11-2808:59:49.390'UNIONALL

SELECT'03916995857','-11-2909:11:05.607'UNIONALL

SELECT'015097712001','-11-3009:13:50.293'

--SQL查询如下:

DECLARE@minDatedatetime,@maxDatedatetime;

SELECT@minDate='-11-1',@maxDate='-12-01';

selectconvert(char(10),dateadd(d,number,@minDate),120),

sum(casewhenconvert(char(8),时间,108)between'00:00'and'01:00'then1else0end)as'00:00~01:00',

sum(casewhenconvert(char(8),时间,108)between'01:00'and'02:00'then1else0end)as'01:00~02:00',

sum(casewhenconvert(char(8),时间,108)between'02:00'and'03:00'then1else0end)as'02:00~03:00',

sum(casewhenconvert(char(8),时间,108)between'03:00'and'04:00'then1else0end)as'03:00~04:00',

sum(casewhenconvert(char(8),时间,108)between'04:00'and'05:00'then1else0end)as'04:00~05:00',

sum(casewhenconvert(char(8),时间,108)between'05:00'and'06:00'then1else0end)as'05:00~06:00',

sum(casewhenconvert(char(8),时间,108)between'06:00'and'07:00'then1else0end)as'06:00~07:00',

sum(casewhenconvert(char(8),时间,108)between'07:00'and'08:00'then1else0end)as'07:00~08:00',

sum(casewhenconvert(char(8),时间,108)between'08:00'and'09:00'then1else0end)as'08:00~09:00',count(a.列名1)as'sum'

from#tbarightjoin

master..spt_valuesbondatediff(d,时间,dateadd(d,number,@minDate))=0

wheredateadd(d,number,@minDate)<=@maxDateandb.type='p'andb.number>=0

groupbyconvert(char(10),dateadd(d,number,@minDate),120)

orderby1

droptable#tb

(13行受影响)

00:00~01:0001:00~02:0002:00~03:0003:00~04:0004:00~05:0005:00~06:0006:00~07:0007:00~08:0008:00~09:00sum

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

-11-010000000112

-11-020000000000

-11-030000000000

-11-040000000000

-11-050000000000

-11-060000000000

-11-070000000000

-11-080000000000

-11-090000000000

-11-100000000000

-11-110000000000

-11-120000000011

-11-130000000000

-11-140000000000

-11-150000000000

-11-160000000000

-11-170000000000

-11-180000000000

-11-190000000000

-11-200000000000

-11-210000000000

-11-220000000011

-11-230000000022

-11-240000000011

-11-250000000011

-11-260000000011

-11-270000000011

-11-280000000011

-11-290000000001

-11-300000000001

-12-010000000000

警告:聚合或其他SET操作消除了空值。

(31行受影响) 代码 -------------------------------------

--Author:liangCK梁爱兰

--Comment:小梁爱兰儿

--Date:-01-0216:47:10

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

-->生成测试数据:#tb

CREATETABLE#tb(列名1varchar(12),时间datetime)

INSERTINTO#tb

SELECT'03174190188','-11-0107:17:39.217'UNIONALL

SELECT'015224486575','-11-0108:01:17.153'UNIONALL

SELECT'013593006926','-11-1208:04:46.560'UNIONALL

SELECT'013599584239','-11-2208:53:27.763'UNIONALL

SELECT'013911693526','-11-2308:53:51.683'UNIONALL

SELECT'013846472440','-11-2308:54:57.233'UNIONALL

SELECT'013990353697','-11-2408:55:25.077'UNIONALL

SELECT'013990353697','-11-2508:56:01.327'UNIONALL

SELECT'013945594843','-11-2608:57:02.233'UNIONALL

SELECT'013990353697','-11-2708:57:29.700'UNIONALL

SELECT'013916597421','-11-2808:59:49.390'UNIONALL

SELECT'03916995857','-11-2909:11:05.607'UNIONALL

SELECT'015097712001','-11-3009:13:50.293'

--SQL查询如下:

DECLARE@minDatedatetime,@maxDatedatetime;

SELECT@minDate='-11-1',@maxDate='-12-01';

selectisnull(convert(char(10),dateadd(d,number,@minDate),120),'sum'),