SQL时间段查询

Perl code

开始时间                结束时间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

SQL code

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)