MySQL如何为各名称补全其最早日期到目标日期间的缺失日期行

核心思路 #

• 第一步：先统计每个name对应的最早日期，作为该用户日期补全的起始边界
• 第二步：生成从全局最早日期到目标结束日期（示例为2021-06-03，线上可替换为CURDATE()取当前日期）的连续日期序列
• 第三步：将所有不重复的name列表和连续日期做笛卡尔积，过滤掉早于对应用户最早日期的记录，即可得到每个用户的完整连续日期序列，需要原表其他字段的话左关联原表即可

MySQL 8.0+ 实现代码（支持递归CTE，无需额外建表） #

假设你的原始表名为user_date，可根据实际表名替换：

WITH RECURSIVE date_range AS (
    -- 递归起始点：取所有用户的最小日期
    SELECT MIN(Date) AS dt FROM user_date
    UNION ALL
    -- 递归生成下一天，直到到达目标结束日期
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_range WHERE dt < '2021-06-03'
),
name_list AS (
    -- 取所有不重复的用户列表
    SELECT DISTINCT name FROM user_date
)
SELECT 
    nl.name,
    dr.dt AS Date
    -- 原表有其他字段的话，在这里加IFNULL(ud.字段名, 默认值) AS 字段名即可
FROM name_list nl
CROSS JOIN date_range dr
-- 过滤早于当前用户最早日期的记录
WHERE dr.dt >= (SELECT MIN(Date) FROM user_date WHERE name = nl.name)
ORDER BY nl.name, dr.dt;

执行后得到的结果和需求完全匹配：CC的日期覆盖2021-05-28到2021-06-03所有日期，AA覆盖2021-06-01到2021-06-03，BB覆盖2021-06-02到2021-06-03。

低版本MySQL（5.x）兼容方案 #

如果数据库不支持递归CTE，建议提前建一张通用辅助日期表，后续所有日期补全需求都可以直接关联使用，性能优于递归方案：

1. 建辅助日期表并预填数据 #

-- 建日期维度表
CREATE TABLE IF NOT EXISTS dim_date (
    dt DATE PRIMARY KEY COMMENT '日期'
);

-- 预插2020-01-01到2030-12-31的所有日期，可按需调整范围
INSERT INTO dim_date(dt)
SELECT DATE_ADD('2020-01-01', INTERVAL (a.num + b.num*10 + c.num*100 + d.num*1000) DAY) AS dt
FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
WHERE DATE_ADD('2020-01-01', INTERVAL (a.num + b.num*10 + c.num*100 + d.num*1000) DAY) <= '2030-12-31';

2. 关联辅助表查询 #

SELECT 
    nl.name,
    dd.dt AS Date
FROM (SELECT DISTINCT name FROM user_date) nl
CROSS JOIN dim_date dd
WHERE dd.dt BETWEEN (SELECT MIN(Date) FROM user_date WHERE name = nl.name) AND '2021-06-03'
ORDER BY nl.name, dd.dt;

内容的提问来源于stack exchange，提问作者Priya Patel