即使没有数据，也显示所有小时

Question

我想写一个查询，即使没有数据，也能显示所有小时。我看到一些帖子建议创建一个临时表，列出所有小时，但我不知道如何做到这一点。这是我目前的查询：

select DATE_FORMAT(t_stamp, "%h %p") as Hour, count(*) as Count
from cyclehistory
where DATE(t_stamp) = CURRENT_DATE()
group by hour(t_stamp)

此工作返回以下

Hour | Count
09 AM | 6
10 AM | 11
1 PM  | 5

但我希望它能退回

Hour | Count
.
.
.
09 AM | 6
10 AM | 11
11 AM | 0
12 PM | 0
1 PM  | 5
.
.

Answer 1

您确实需要某种的数字表才能做到这一点。在这里，你只需要24个数字，从0到23。假设数字表称为numbers，列为num:

select maketime(n.num, 0, 0) as t_time, count(c.t_stamp) cnt
from numbers n
left join cyclehistory c 
on hour(c.tstamp) = n.num
and c.t_stamp >= current_date and c.t_stamp < current_date + interval 1 day
group by n.num

有多种方法可以构建数字表。您可以手动创建表格并插入记录：

create table numbers (num int primary key);
insert into numbers values (0), (1), ..., (23);

对于一次性任务，可以直接在查询中创建派生表：

select ...
from (
select 0 num
union all select 1
...
union all select 23
) numbers
left join ...

在MySQL 8.0中，您可以使用递归查询：

with recursive numbers (num) as (
select 0
union all select num + 1 from cte where num < 23
)
select ...
from numbers 
left join ...