日期:2014-05-18  浏览次数:20555 次

SQL时间段查询
Perl code
开始时间                结束时间
2011-05-16 00:10:00    2011-05-16 02:00:00
2011-05-16 02:00:00    2011-05-16 03:00:00
2011-05-16 02:10:00    2011-05-16 03:00:00
2011-05-16 03:00:00    2011-05-16 04:00:00
2011-05-16 05:00:00    2011-05-16 07:00:00
2011-05-16 07:00:00    2011-05-16 08:00:00
2011-05-16 08:00:00    2011-05-16 10:00:00
我想得出:
0-2点    2-3点   3-4点  5-7点 7-8点  8-10点 10-12点
1         2       1      1     1       1       0


------解决方案--------------------
SQL code

create table #tb
(开始时间 datetime, 结束时间 datetime)
insert #tb
select '2011-05-16 00:10:00','2011-05-16 02:00:00' union all
select '2011-05-16 02:00:00','2011-05-16 03:00:00' union all
select '2011-05-16 02:10:00','2011-05-16 03:00:00' union all
select '2011-05-16 03:00:00','2011-05-16 04:00:00' union all
select '2011-05-16 05:00:00','2011-05-16 07:00:00' union all
select '2011-05-16 07:00:00','2011-05-16 08:00:00' union all
select '2011-05-16 08:00:00','2011-05-16 10:00:00'

declare @sql as nvarchar(max)
set @sql=''
select @sql=@sql+',sum(case when ltrim(datepart(hh,开始时间))+''-''+ltrim(datepart(hh,结束时间))='''+[time]+''' then 1 else 0 end) as '''+[time]+'点'''
       from (select distinct ltrim(datepart(hh,开始时间))+'-'+ltrim(datepart(hh,结束时间)) as [time] from #tb) as T
set @sql='select '+stuff(@sql,1,1,'')+' from #tb'
exec(@sql)