在sqlserver2008中求一个sql，在某个日期内在2个小时之内出现的次数。(急)

[快跑蜗牛哥]

已知表 states
栏位
id time
1 2017-06-21 14:58:02.000
2 2016-11-24 23:35:16.000
3 2017-04-24 11:12:38.000
4 2016-12-10 10:09:07.000
5 2017-06-19 14:53:30.000
6 2016-12-09 15:34:45.000
7 2017-04-24 11:41:46.000

要求得到，某两个日期之间
2,4,6,8,10,12,14,16,18,20,22,24 小时之内出现的次数，
如 日期范围 2017-06-02 到 2016-07-02
要求得到 0点到2点 出现的次数
2点到4点 出现的次数
4点到6点 出现的次数
6点到8点 出现的次数
……
当次数为空时，显示 0

[二月十六]

下边描述的和上边的这个表什么关系？某两个日期之间的数量

[骑着蜗牛去爬山]

with states(id,[time]) as(
select 1,'2017-06-21 14:58:02.000' union all
select 2,'2016-11-24 23:35:16.000' union all
select 3,'2017-04-24 11:12:38.000' union all
select 4,'2016-12-10 10:09:07.000' union all
select 5,'2017-06-19 14:53:30.000' union all
select 6,'2016-12-09 15:34:45.000' union all
select 7,'2017-04-24 11:41:46.000'
)

select (number-1)*2 [start],number*2 [end],sum(case when datename(hour,[time]) between (number-1)*2 and number*2 then 1 else 0 end) [ct] from states,master..spt_values where [time] between '2016-07-02' and '2017-06-02'
and type='p' and number between 1 and 12 group by (number-1)*2,number*2

[快跑蜗牛哥]

引用 1 楼 sinat_28984567 的回复:

下边描述的和上边的这个表什么关系？某两个日期之间的数量

对，是某两个日期之间，每隔两小时出现的次数。

[唐诗三百首]

create table states([id] int,[time] datetime)

insert into states([id],[time])
 select 1,'2017-06-21 14:58:02.000' union all
 select 2,'2016-11-24 23:35:16.000' union all
 select 3,'2017-04-24 11:12:38.000' union all
 select 4,'2016-12-10 10:09:07.000' union all
 select 5,'2017-06-19 14:53:30.000' union all
 select 6,'2016-12-09 15:34:45.000' union all
 select 7,'2017-04-24 11:41:46.000'



declare @begindate datetime,@enddate datetime

select @begindate='2016-06-02',
       @enddate='2017-07-02'

select [timerange]=rtrim(t.st)+'点到'+rtrim(t.et)+'点',
       [qty]=isnull(u.qty,0)
 from (select 'st'=a.number,
              'et'=a.number+2
        from master.dbo.spt_values a
        where a.type='P' 
        and a.number between 0 and 22
        and a.number%2=0) t
 outer apply(select qty=count(1)
              from states b 
              where b.[time] between @begindate and @enddate
              and datepart(hh,b.[time]) between t.st and t.et) u

/*
timerange                      qty
------------------------------ -----------
0点到2点                          0
2点到4点                          0
4点到6点                          0
6点到8点                          0
8点到10点                         1
10点到12点                        3
12点到14点                        2
14点到16点                        3
16点到18点                        0
18点到20点                        0
20点到22点                        0
22点到24点                        1

(12 row(s) affected)
*/

[快跑蜗牛哥]

我现在可以单独的查出某个时间段的数据， 如何拼接起来？

引用 2 楼 hjywyj 的回复:

with states(id,[time]) as(
select 1,'2017-06-21 14:58:02.000' union all
select 2,'2016-11-24 23:35:16.000' union all
select 3,'2017-04-24 11:12:38.000' union all
select 4,'2016-12-10 10:09:07.000' union all
select 5,'2017-06-19 14:53:30.000' union all
select 6,'2016-12-09 15:34:45.000' union all
select 7,'2017-04-24 11:41:46.000'
)

select (number-1)*2 [start],number*2 [end],sum(case when datename(hour,[time]) between (number-1)*2 and number*2 then 1 else 0 end) [ct] from states,master..spt_values where [time] between '2016-07-02' and '2017-06-02'
and type='p' and number between 1 and 12 group by (number-1)*2,number*2

@骑着蜗牛去爬山 这个 type=‘p’ 是怎么来的？ 我能得到 单独的某个时间段内的次数，怎么用一条sql语句实现啊 以下是得到 10点到12点 出现的次数。 select count(*) as '12' from states where convert(varchar(10),[Time],120) between '2016-07-02' and '2017-06-02' and datepart(hh,[Time]) between 10 and 12

[快跑蜗牛哥]

@唐诗三百首 @骑着蜗牛去爬山 @二月十六 非常感谢几位大神，已经满足要求了，谢谢！！