我现在有一张表: 用户输入一个时间段 ,比如开始时间 2009-12-1 结束时间 2009-12-30 这样。 方法1:
--参考这个 --> 测试数据:@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 ------------------------------------- --> 生成测试数据: #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查询如下: 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;
结果:
------------------------------------- -- Author : liangCK 梁爱兰 -- Comment: 小梁 爱 兰儿 -- Date : 2010-01-02 16:47:10 ------------------------------------- --> 生成测试数据: #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查询如下: 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 ------------------------------------- --> 生成测试数据: #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查询如下: 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 ------------------------------------- --> 生成测试数据: #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查询如下: 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 行受影响)
转自:http://topic.csdn.net/u/20100102/16/bf7811f6-b79b-4221-9ee9-42ae0b8e1c6c.html
|
|
来自: icecity1306 > 《数据库资料》