sd2208464 于 2016.03.22 12:14 提问

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

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   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 已通过测试

enpterexpress   2016.03.22 12:56
sd2208464 没有需要的功能。

HTBALC   2016.03.22 14:20

ch21st   2016.03.23 15:57

`````` select DATEPART(hh,[time]),count(title) from tests
group by CONVERT(varchar,[time],102),DATEPART(hh,[time])
``````
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   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   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      2016.03.22 13:01

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 这样的方式只能获取到有数据的时间。比如1点、2点、9点。其他的都没有。需求是没有数据的点值为0