MySQL：如何一次性统计24小时各时段符合条件的broadcasts表行数

我懂你现在的麻烦——循环跑24次查询太折腾了，咱们用一次SQL就能搞定全天的统计需求！核心思路是先生成包含0到23小时的临时小时列表，再关联你的broadcasts表做批量统计。

完整SQL语句（MySQL 8.0+适用） #

WITH hours AS (
    SELECT 0 AS hour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
    SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
    SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
    SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
)
SELECT
    h.hour,
    COUNT(b.id) AS total_online
FROM hours h
LEFT JOIN broadcasts b ON
    -- 条件1：start_time的小时与当前统计小时匹配
    HOUR(b.start_time) = h.hour
    -- 条件2：end_time大于等于当前统计小时的起始时间点（比如当天的0点、1点...）
    OR b.end_time >= CONCAT(DATE(NOW()), ' ', LPAD(h.hour, 2, '0'), ':00:00')
    -- 可选：限制统计当天的记录，换成指定日期即可统计历史数据
    AND DATE(b.start_time) = DATE(NOW())
GROUP BY h.hour
ORDER BY h.hour;

关键细节解释 #

1. 生成小时序列：
   用WITH子句创建临时表hours，包含0到23的所有小时数。如果你的MySQL版本低于8.0（不支持CTE），可以把这部分换成子查询写法（见下方适配版）。

2. 关联与统计逻辑：

   • 用LEFT JOIN保证哪怕某小时没有符合条件的记录，也会返回total_online=0，不会缺失该行数据
   • 两个条件用OR连接，覆盖你要求的两种场景：start_time小时匹配，或者end_time晚于该小时起始点
   • 最后的AND DATE(b.start_time) = DATE(NOW())是可选过滤条件，若要统计特定日期，把DATE(NOW())换成目标日期字符串（比如'2024-05-20'）即可

3. 低版本MySQL适配写法（<8.0）
   如果你的数据库不支持WITH子句，直接用子查询替代：

   SELECT
       h.hour,
       COUNT(b.id) AS total_online
   FROM (
       SELECT 0 AS hour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
       SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
       SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
       SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
       SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
       SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
   ) h
   LEFT JOIN broadcasts b ON
       HOUR(b.start_time) = h.hour
       OR b.end_time >= CONCAT('2024-05-20', ' ', LPAD(h.hour, 2, '0'), ':00:00')
       AND DATE(b.start_time) = '2024-05-20'
   GROUP BY h.hour
   ORDER BY h.hour;
   

这样就不用重复跑24次查询，一次就能拿到全天每个小时的统计结果啦！

内容的提问来源于stack exchange，提问作者Space Chocolate