用sql语句按指定时间段分组统计
我现在有一张表：
列名1 时间
031741901882009-11-01 07:17:39.217
0152244865752009-11-01 08:01:17.153
0135930069262009-11-12 08:04:46.560
0135995842392009-11-22 08:53:27.763
0139116935262009-11-23 08:53:51.683
0138464724402009-11-23 08:54:57.233
0139903536972009-11-24 08:55:25.077
0139903536972009-11-25 08:56:01.327
0139455948432009-11-26 08:57:02.233
0139903536972009-11-27 08:57:29.700
0139165974212009-11-28 08:59:49.390
039169958572009-11-29 09:11:05.607
0150977120012009-11-30 09:13:50.293
现在想要做一个报表：
时段 2009-11-1 2009-11-2 2009-11-3 合计
00:00-01:00000 0
01:00-02:00000 0
02:00-03:00000 0
03:00-04:00000 0
04:00-05:00000 0
05:00-06:00000 0
06:00-07:00011 2
07:00-08:00144 9
08:00-09:00111613 .
09:00-10:00112613 .
10:00-11:00122925 .
11:00-12:006711 .
12:00-13:00492
13:00-14:0051011
14:00-15:00131623
15:00-16:0014517
16:00-17:0010518
17:00-18:00736
18:00-19:00725
19:00-20:00405
20:00-21:00530
21:00-22:00200
22:00-23:00210
23:00-24:00000
合计 114 137 154 405
希望大家帮帮我。
我希望是用sql语句就搞定的，便于扩展，但不想用临时表 或 UNION All
[解决办法]
--参考这个--> 测试数据：@tabledeclare @table table([id] int,[day] varchar(10),[starttime] varchar(10),[overtime] varchar(10),[name] varchar(10))insert @tableselect 1,'20091202', '09:00','16:00','张三'declare @begdate datetime,@enddate datetimeselect @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_valueswhere 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.namefrom 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]) uon t.[date] = u.[date] and t.[time] = u.[time]--select * from #tempdeclare @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) tselect @sql = 'select [time] '+ @sql + ' from #temp group by [time]'--print @sqlexec(@sql)drop table #temp
[解决办法]
--------------------------------------- Author : liangCK 梁爱兰-- Comment: 小梁 爱 兰儿-- Date : 2010-01-02 16:47:10------------------------------------- --> 生成测试数据: #tbCREATE TABLE #tb(列名1 varchar(12),时间 datetime)INSERT INTO #tbSELECT '03174190188','2009-11-01 07:17:39.217' UNION ALLSELECT '015224486575','2009-11-01 08:01:17.153' UNION ALLSELECT '013593006926','2009-11-12 08:04:46.560' UNION ALLSELECT '013599584239','2009-11-22 08:53:27.763' UNION ALLSELECT '013911693526','2009-11-23 08:53:51.683' UNION ALLSELECT '013846472440','2009-11-23 08:54:57.233' UNION ALLSELECT '013990353697','2009-11-24 08:55:25.077' UNION ALLSELECT '013990353697','2009-11-25 08:56:01.327' UNION ALLSELECT '013945594843','2009-11-26 08:57:02.233' UNION ALLSELECT '013990353697','2009-11-27 08:57:29.700' UNION ALLSELECT '013916597421','2009-11-28 08:59:49.390' UNION ALLSELECT '03916995857','2009-11-29 09:11:05.607' UNION ALLSELECT '015097712001','2009-11-30 09:13:50.293'--SQL查询如下: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 BON CONVERT(varchar(8),B.时间,108) >= A.MinDate AND CONVERT(varchar(8),B.时间,108) < A.MaxDateGROUP BY A.时段 WITH ROLLUP;'EXEC sp_executesql @cmd,N'@minDate datetime,@maxDate datetime',@minDate,@maxDate;DROP TABLE #tb;
[解决办法]
时段 2009-11-01 2009-11-02 2009-11-03 2009-11-04 2009-11-05 2009-11-06 2009-11-07 2009-11-08 2009-11-09 2009-11-10 2009-11-11 2009-11-12 2009-11-13 2009-11-14 2009-11-15 2009-11-16 2009-11-17 2009-11-18 2009-11-19 2009-11-20 2009-11-21 2009-11-22 2009-11-23 2009-11-24 2009-11-25 2009-11-26 2009-11-27 2009-11-28 2009-11-29 2009-11-30 2009-12-01 合计00:00~01:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 001:00~02:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 002:00~03:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 003:00~04:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 004:00~05:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 005:00~06:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 006:00~07:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 007:00~08:00 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 108:00~09:00 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 2 1 1 1 1 1 0 0 0 1009:00~10:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 210:00~11:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 011:00~12:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 012:00~13:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 013:00~14:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 014:00~15:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 015:00~16:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 016:00~17:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 017:00~18:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 018:00~19:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 019:00~20:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 020:00~21:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 021:00~22:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 022:00~23:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 023:00~24:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0合计 2 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 2 1 1 1 1 1 1 1 0 13
[解决办法]
--------------------------------------- Author : liangCK 梁爱兰-- Comment: 小梁 爱 兰儿-- Date : 2010-01-02 16:47:10------------------------------------- --> 生成测试数据: #tbCREATE TABLE #tb(列名1 varchar(12),时间 datetime)INSERT INTO #tbSELECT '03174190188','2009-11-01 07:17:39.217' UNION ALLSELECT '015224486575','2009-11-01 08:01:17.153' UNION ALLSELECT '013593006926','2009-11-12 08:04:46.560' UNION ALLSELECT '013599584239','2009-11-22 08:53:27.763' UNION ALLSELECT '013911693526','2009-11-23 08:53:51.683' UNION ALLSELECT '013846472440','2009-11-23 08:54:57.233' UNION ALLSELECT '013990353697','2009-11-24 08:55:25.077' UNION ALLSELECT '013990353697','2009-11-25 08:56:01.327' UNION ALLSELECT '013945594843','2009-11-26 08:57:02.233' UNION ALLSELECT '013990353697','2009-11-27 08:57:29.700' UNION ALLSELECT '013916597421','2009-11-28 08:59:49.390' UNION ALLSELECT '03916995857','2009-11-29 09:11:05.607' UNION ALLSELECT '015097712001','2009-11-30 09:13:50.293'--SQL查询如下: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 @maxDateGROUP 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------------------------------------- --> 生成测试数据: #tbCREATE TABLE #tb(列名1 varchar(12),时间 datetime)INSERT INTO #tbSELECT '03174190188','2009-11-01 07:17:39.217' UNION ALLSELECT '015224486575','2009-11-01 08:01:17.153' UNION ALLSELECT '013593006926','2009-11-12 08:04:46.560' UNION ALLSELECT '013599584239','2009-11-22 08:53:27.763' UNION ALLSELECT '013911693526','2009-11-23 08:53:51.683' UNION ALLSELECT '013846472440','2009-11-23 08:54:57.233' UNION ALLSELECT '013990353697','2009-11-24 08:55:25.077' UNION ALLSELECT '013990353697','2009-11-25 08:56:01.327' UNION ALLSELECT '013945594843','2009-11-26 08:57:02.233' UNION ALLSELECT '013990353697','2009-11-27 08:57:29.700' UNION ALLSELECT '013916597421','2009-11-28 08:59:49.390' UNION ALLSELECT '03916995857','2009-11-29 09:11:05.607' UNION ALLSELECT '015097712001','2009-11-30 09:13:50.293'--SQL查询如下: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)) = 0where dateadd(d,number,@minDate) <= @maxDate and b.type = 'p' and b.number >= 0group by convert(char(10),dateadd(d,number,@minDate),120)with rolluporder by 1drop 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 22009-11-02 0 0 0 0 0 0 0 0 0 02009-11-03 0 0 0 0 0 0 0 0 0 02009-11-04 0 0 0 0 0 0 0 0 0 02009-11-05 0 0 0 0 0 0 0 0 0 0......2009-11-28 0 0 0 0 0 0 0 0 1 12009-11-29 0 0 0 0 0 0 0 0 0 12009-11-30 0 0 0 0 0 0 0 0 0 12009-12-01 0 0 0 0 0 0 0 0 0 0sum 0 0 0 0 0 0 0 1 10 13 (32 行受影响)
