问题描述

SQL SERVER 统计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

解决方案

select sv.number, sum(case when t.time IS NOT NULL THEN 1 ELSE 0 end) value
FROM master..spt_values AS sv

LEFT JOIN tests AS t ON sv.number=DATEPART(hh,t.time)
WHERE sv.type='P' and sv.number BETWEEN 0 AND 23
group by sv.number

解决方案二：

SqlServer 根据字段分类汇总信息

解决方案三：

对于0可以用when else啊

解决方案四：

不在一天的要分开统计吧？

 select DATEPART(hh,[time]),count(title) from tests
group by CONVERT(varchar,[time],102),DATEPART(hh,[time])

解决方案五：

如果没有的时间也要，改成：

 select sv.number ,count(title)
FROM master..spt_values AS sv
LEFT JOIN tests AS t ON sv.number=t.DATEPART(hh,[time])
WHERE sv.type='P'  and sv.number BETWEEN 0 AND 24
group by CONVERT(varchar,[time],102),DATEPART(hh,[time])

解决方案六：

如果没有的时间也要，改成：

 select sv.number ,count(title)
FROM master..spt_values AS sv
LEFT JOIN tests AS t ON sv.number=t.DATEPART(hh,[time])
WHERE sv.type='P'  and sv.number BETWEEN 0 AND 24
group by CONVERT(varchar,[time],102),DATEPART(hh,[time])

解决方案七：

上面的没有测试：

 WITH tests([time],title) AS (
    SELECT '2016-03-22 1:12:00','a' UNION ALL
    SELECT '2016-03-22 2:12:00','b' UNION ALL
    SELECT '2016-03-22 9:12:00','c'
    )

select sv.number ,count(title)
FROM master..spt_values AS sv
LEFT JOIN tests AS t ON sv.number=DATEPART(hh,t.[time])
WHERE sv.type='P'  and sv.number BETWEEN 0 AND 24
group by CONVERT(varchar,[time],102), sv.number

解决方案八：

用substring等，取出小时，然后再对它进行group by 分组