表结构如下
航班号 FlightNo
值机时间 CheckTime
现在想按小时统计6点到22点每个时间段的值机人数，例如6-7点、7-8点等等。
当然可以把每个时间段单独统计一遍，但就想问问有没有更简洁的解决办法？
------解决方案--------------------
按小时分组就可以实现啊，如果跨天，就按天、小时分组
------解决方案--------------------
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 行受影响)