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 行受影响)
