(SQL语句)按指定时间段分组统计
生活随笔
收集整理的這篇文章主要介紹了
(SQL语句)按指定时间段分组统计
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
我現(xiàn)在有一張表：
? 列名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
現(xiàn)在想要做一個報表：
時段 ? ? 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 結(jié)束時間 2009-12-30 這樣。
方法1:
?
代碼 --參考這個-->?測試數(shù)據(jù)：@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
-------------------------------------
?
-->?生成測試數(shù)據(jù):?#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查詢?nèi)缦?
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;
?
?
結(jié)果:
?
|
?
?
代碼 ---------------------------------------??Author?:?liangCK?梁愛蘭
--??Comment:?小梁?愛?蘭兒
--??Date???:?2010-01-02?16:47:10
-------------------------------------
?
-->?生成測試數(shù)據(jù):?#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查詢?nèi)缦?
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
-------------------------------------
?
-->?生成測試數(shù)據(jù):?#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查詢?nèi)缦?
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
-------------------------------------
?
-->?生成測試數(shù)據(jù):?#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查詢?nèi)缦?
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?行受影響)
?
?
轉(zhuǎn)自:http://topic.csdn.net/u/20100102/16/bf7811f6-b79b-4221-9ee9-42ae0b8e1c6c.html
?
點擊下載:用sql語句按指定時間段分組統(tǒng)計
轉(zhuǎn)載于:https://www.cnblogs.com/jhxk/articles/1639384.html
總結(jié)
以上是生活随笔為你收集整理的(SQL语句)按指定时间段分组统计的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 建行购车分期卡是信用卡吗？建行购车分期付
- 下一篇： 建行分期通不用会怎样？里面的套路满满