如何统计24小时内，每小时发布的条数？

[倾斜的水瓶座]

如何统计24小时内，每小时发布的条数？


想得到如下结果：

number value
0 0
1 1
2 1
3 0
4 0
5 0
6 0
7 0
8 0
9 1
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
21 0
22 0
23 0

[倾斜的水瓶座]

引用 1 楼 yooq_csdn 的回复:

select  substring(convert(varchar(50),time,120),1,10),substring(convert(varchar(50),time,120),12,2),count(*)
from  table
group by substring(convert(varchar(50),time,120),1,10),substring(convert(varchar(50),time,120),12,2)
order by 1,2

不对。这样不是从0到23点的。你这样只统计出了有数据的时间。

[spiritofdragon]

with tests(time,title) as (
select '2016-03-22 01:02:00.000','a' union all
select '2016-03-22 02:02:00.000','b' union all
select '2016-03-22 09:02:00.000','x' 
),lst as (
select number from master..spt_values where type='p' and number<24
),t as (
select datepart(HH,tests.time) h,COUNT(1) cnt
from tests
where time between CAST(GETDATE() AS date) and GETDATE()
group by datepart(HH,tests.time) 
)

select lst.number,isnull(t.cnt,0) value from lst left join t on lst.number=t.h

[Ekun_sky]

with a(time,title) as (
select '2016-03-22 01:02:00.000','a' union all
select '2016-03-22 02:02:00.000','b' union all
select '2016-03-22 09:02:00.000','x')

select aa.number,isnull(bb.value,0) as value from
(select number+1 as number from master..spt_values where type='p' and number<24) as aa left join 
(select DATEPART(hh,time) as time,COUNT(*) as value from a group by DATEPART(hh,time)) as bb on
aa.number=bb.time

[yooq_csdn]

select  substring(convert(varchar(50),time,120),1,10),substring(convert(varchar(50),time,120),12,2),count(*)
from  table
group by substring(convert(varchar(50),time,120),1,10),substring(convert(varchar(50),time,120),12,2)
order by 1,2