2 sd2208464 sd2208464 于 2016.03.22 12:14 提问

SQL SERVER 统计24小时内每小时添加的数据 1C

如何统计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

8个回答

curry2016
curry2016   2016.05.19 09:47

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

mcj2052239
mcj2052239 已通过测试
一年多之前 回复
enpterexpress
enpterexpress   2016.03.22 12:56
sd2208464
sd2208464 没有需要的功能。
一年多之前 回复
HTBALC
HTBALC   2016.03.22 14:20

对于0可以用when else啊

ch21st
ch21st   2016.03.23 15:57

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

 select DATEPART(hh,[time]),count(title) from tests
group by CONVERT(varchar,[time],102),DATEPART(hh,[time])
ch21st
ch21st   2016.03.23 16:02

如果没有的时间也要,改成:

 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])
ch21st
ch21st   2016.03.23 16:02

如果没有的时间也要,改成:

 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])
ch21st
ch21st   2016.03.23 16:06

上面的没有测试:

 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
oyljerry
oyljerry   Ds   Rxr 2016.03.22 13:01

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

HTBALC
HTBALC select * from ( select nday, ntime,count(user_id) as num from ( select distinct to_char(time, 'yyyy-mm-dd') as nday, to_char(time, 'HH24') as ntime, title from 表名 where 1 = 1 and time >= date'2016-02-25' and time < date'2016-02-26' ) group by nday, ntime ) pivot(sum(num) for ntime in (00,01, 02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21, 22,23))
一年多之前 回复
sd2208464
sd2208464 这样的方式只能获取到有数据的时间。比如1点、2点、9点。其他的都没有。需求是没有数据的点值为0
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!