求教一个按小时统计的sql

[woshizhaoxuhui]

表结构如下
航班号 FlightNo
值机时间 CheckTime
现在想按小时统计6点到22点每个时间段的值机人数，例如6-7点、7-8点等等。
当然可以把每个时间段单独统计一遍，但就想问问有没有更简洁的解决办法？

[哥眼神纯洁不]

按小时分组就可以实现啊，如果跨天，就按天、小时分组

[专注or全面]

create table #t
(
	no varchar(20),
	currentDate datetime
)

insert into #t values ('A001','2014-6-25 13:00:00')

insert into #t values ('A001','2014-6-25 14:00:00')
insert into #t values ('A002','2014-6-25 14:00:00')

insert into #t values ('A001','2014-6-25 15:00:00')
insert into #t values ('A002','2014-6-25 15:00:00')
insert into #t values ('A003','2014-6-25 15:00:00')

select * from #t

select distinct CONVERT(varchar(12) , currentDate, 102 ),
CONVERT(varchar(12) , currentDate, 108 )+'~'+CONVERT(varchar(12) , DateAdd(Hour,1,currentDate), 108 ) as currentdate ,
COUNT(1)over(partition by cast(currentDate as varchar(13))) as total
from #t
where currentDate>='2014-6-25 06:00:00' and currentDate<='2014-6-25 22:00:00'

currentdate  currentdate               total
------------ ------------------------- -----------
2014.06.25   13:00:00~14:00:00         1
2014.06.25   14:00:00~15:00:00         2
2014.06.25   15:00:00~16:00:00         3

(3 行受影响)