考虑使用数字表 - 请参阅下面的链接。这将为您提供目前所需的输出:
测试数据:
CREATE TABLE T (`id` int, `dt` datetime, `val` int);
INSERT INTO T (`id`, `dt`, `val`)
VALUES
(0, '2018-02-19 00:01:00', 10),
(1, '2018-02-19 00:02:00', 10),
(2, '2018-02-19 00:03:00', 10),
(4, '2018-02-19 01:01:00', 10),
(5, '2018-02-19 01:02:00', 10),
(6, '2018-02-19 02:03:00', 10)
;
Sql:
select
lpad(cast(HH as char(2)),2,'0') as hour,
sum(val) as sumVal from
(
select
EXTRACT(HOUR from dt) AS HH,
val
from T
WHERE dt >= '2018-02-19 0:0:0' and dt < '2018-02-20 0:0:0'
UNION ALL SELECT 0,0
UNION ALL SELECT 1,0 UNION ALL SELECT 2,0 UNION ALL SELECT 3,0
UNION ALL SELECT 4,0 UNION ALL SELECT 5,0 UNION ALL SELECT 6,0
UNION ALL SELECT 7,0 UNION ALL SELECT 8,0 UNION ALL SELECT 9,0
UNION ALL SELECT 10,0 UNION ALL SELECT 11,0 UNION ALL SELECT 12,0
UNION ALL SELECT 13,0 UNION ALL SELECT 14,0 UNION ALL SELECT 15,0
UNION ALL SELECT 16,0 UNION ALL SELECT 17,0 UNION ALL SELECT 18,0
UNION ALL SELECT 19,0 UNION ALL SELECT 20,0 UNION ALL SELECT 21,0
UNION ALL SELECT 22,0 UNION ALL SELECT 23,0
) as m
group by lpad(cast(HH as char(2)),2,'0')
为您提供以下输出:
hour sumVal
00 30
01 20
02 10
03 0
04 0
05 0
06 0
07 0
08 0
09 0
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
如果您更频繁地需要此操作,请创建一个数字表并使用已有的联接语法。如果没有数字表,您也可以在不加入的情况下联合所有内容。
SO-阅读:
- MYSQL:序号表 https://stackoverflow.com/questions/14298154/mysql-sequential-number-table
- 在mysql中创建一个“数字表” https://stackoverflow.com/questions/9751318/creating-a-numbers-table-in-mysql
使用数字表,以上所有大联合可以重写为
UNION ALL SELECT num, 0 from numbers where num between 0 and 23
或者您可以对其使用连接和合并语法。