如何将MySQL GROUP BY结果扩展至完整整数域作为轴？

一、数值型连续维度（如1-100的user_score） #

核心思路是先生成包含所有目标数值的连续序列，再左连接原聚合结果，用COALESCE将缺失项的计数转为0。

方法1：使用递归CTE（MySQL 8.0及以上版本支持） #

递归CTE可以快速生成连续整数序列，无需额外建表：

WITH RECURSIVE score_range AS (
    SELECT 1 AS user_score
    UNION ALL
    SELECT user_score + 1 FROM score_range WHERE user_score < 100
)
SELECT 
    sr.user_score, 
    COALESCE(t.cnt, 0) AS cnt
FROM score_range sr
LEFT JOIN (
    -- 原聚合查询
    SELECT user_score, COUNT(*) AS cnt FROM t GROUP BY user_score
) t ON sr.user_score = t.user_score
ORDER BY sr.user_score;

方法2：使用临时表（兼容MySQL 5.x版本） #

如果无法使用CTE，可创建临时表存储所有目标数值，再进行关联：

-- 创建临时表并插入1-100的整数
CREATE TEMPORARY TABLE score_range (user_score INT PRIMARY KEY);
INSERT INTO score_range VALUES 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
-- 省略中间数值，可通过循环或批量插入完成
(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);

-- 关联查询补全缺失值
SELECT 
    sr.user_score, 
    COALESCE(t.cnt, 0) AS cnt
FROM score_range sr
LEFT JOIN (
    SELECT user_score, COUNT(*) AS cnt FROM t GROUP BY user_score
) t ON sr.user_score = t.user_score
ORDER BY sr.user_score;

二、时间型连续维度（如1-24点的时间轴） #

同样基于「基准序列+左连接」的思路，根据时间字段的格式选择对应生成方式。

场景1：时间字段为字符串格式（如"1:00"） #

WITH RECURSIVE hour_range AS (
    SELECT 1 AS hour_num
    UNION ALL
    SELECT hour_num + 1 FROM hour_range WHERE hour_num < 24
)
SELECT 
    CONCAT(hr.hour_num, ':00') AS time, 
    COALESCE(t.count, 0) AS count
FROM hour_range hr
LEFT JOIN (
    -- 原时间聚合查询
    SELECT time, COUNT(*) AS count FROM time_table GROUP BY time
) t ON CONCAT(hr.hour_num, ':00') = t.time
ORDER BY hr.hour_num;

场景2：时间字段为TIME类型 #

生成标准TIME格式的连续小时序列，再进行关联：

WITH RECURSIVE hour_range AS (
    SELECT MAKETIME(1, 0, 0) AS hour_time
    UNION ALL
    SELECT ADDTIME(hour_time, MAKETIME(1, 0, 0)) 
    FROM hour_range 
    WHERE hour_time < MAKETIME(24, 0, 0)
)
SELECT 
    hr.hour_time AS time, 
    COALESCE(t.count, 0) AS count
FROM hour_range hr
LEFT JOIN (
    SELECT time, COUNT(*) AS count FROM time_table GROUP BY time
) t ON hr.hour_time = t.time
ORDER BY hr.hour_time;

内容的提问来源于stack exchange，提问作者Rick Dou