SQL时间段查询
开始时间 结束时间2011-05-16 00:10:00 2011-05-16 02:00:002011-05-16 02:00:00 2011-05-16 03:00:002011-05-16 02:10:00 2011-05-16 03:00:002011-05-16 03:00:00 2011-05-16 04:00:002011-05-16 05:00:00 2011-05-16 07:00:002011-05-16 07:00:00 2011-05-16 08:00:002011-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
create table #tb(开始时间 datetime, 结束时间 datetime)insert #tbselect '2011-05-16 00:10:00','2011-05-16 02:00:00' union allselect '2011-05-16 02:00:00','2011-05-16 03:00:00' union allselect '2011-05-16 02:10:00','2011-05-16 03:00:00' union allselect '2011-05-16 03:00:00','2011-05-16 04:00:00' union allselect '2011-05-16 05:00:00','2011-05-16 07:00:00' union allselect '2011-05-16 07:00:00','2011-05-16 08:00:00' union allselect '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 Tset @sql='select '+stuff(@sql,1,1,'')+' from #tb'exec(@sql)
