MySQL 5.7中如何找出PersonalActivity表的缺失月份日期区间

要解决这个问题，我们需要分步骤生成时间范围内的所有年月、定位已被覆盖的年月、筛选出缺失的年月，最后将连续的缺失年月合并成完整区间。由于MySQL 5.7不支持递归CTE和窗口函数（比如LAG），我们会用变量和数字辅助表来实现需求。

完整解决方案代码 #

-- 初始化变量：获取数据的时间起止范围
SET @min_date = (SELECT MIN(FromDate) FROM PersonalActivity);
SET @max_date = (SELECT MAX(ToDate) FROM PersonalActivity);
SET @prev_date = NULL;
SET @group = 0;
SET @row := -1;
SET @row2 := -1;

-- 最终查询：生成缺失的年月区间
SELECT 
    MIN(missing_month) AS FromDate,
    MAX(missing_month) AS ToDate,
    'N/A' AS Subject
FROM (
    SELECT 
        missing_month,
        date_val,
        -- 标记连续年月的分组：如果当前年月和前一个连续，分组ID不变，否则+1
        @group := IF(@prev_date IS NOT NULL AND DATE_ADD(@prev_date, INTERVAL 1 MONTH) = date_val, @group, @group + 1) AS group_id,
        @prev_date := date_val AS prev_date
    FROM (
        -- 第一步：筛选出所有缺失的年月
        SELECT 
            year_month AS missing_month,
            STR_TO_DATE(year_month, '%Y-%m') AS date_val
        FROM (
            -- 生成从最小日期到最大日期的所有年月
            SELECT 
                DATE_FORMAT(DATE_ADD(@min_date, INTERVAL num MONTH), '%Y-%m') AS year_month
            FROM (
                -- 生成0-99的数字序列（足够覆盖100个月的跨度，可按需扩展）
                SELECT 
                    @row := @row + 1 AS num
                FROM 
                    (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) t1,
                    (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) t2
            ) nums
            WHERE 
                DATE_ADD(@min_date, INTERVAL num MONTH) <= @max_date
        ) all_months
        LEFT JOIN (
            -- 第二步：获取所有已被现有数据覆盖的年月
            SELECT DISTINCT
                DATE_FORMAT(month_date, '%Y-%m') AS covered_month
            FROM (
                -- 把原表的每个日期区间展开为单个年月
                SELECT 
                    DATE_ADD(pa.FromDate, INTERVAL m.num MONTH) AS month_date
                FROM 
                    PersonalActivity pa
                JOIN (
                    SELECT 
                        @row2 := @row2 + 1 AS num
                    FROM 
                        (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) t1,
                        (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) t2
                ) m ON DATE_ADD(pa.FromDate, INTERVAL m.num MONTH) <= pa.ToDate
            ) covered
            ON all_months.year_month = covered.covered_month
        WHERE 
            -- 筛选出未被覆盖的年月
            covered.covered_month IS NULL
        ORDER BY date_val
    ) missing_dates
) grouped
-- 按分组ID合并连续年月为区间
GROUP BY group_id
ORDER BY FromDate;

代码分步解释 #

1. 生成数字序列：通过两个10行的子查询交叉连接，生成0到99的数字，用来扩展日期区间和生成所有年月。如果你的数据跨度超过100个月，可以增加更多UNION ALL SELECT来扩展数字范围。

2. 生成全量年月：基于表中最早的FromDate和最晚的ToDate，用数字序列生成这个范围内的所有YYYY-MM格式年月。

3. 获取已覆盖年月：将原表中的每个日期区间展开为单个年月，去重后得到所有已被占用的年月。

4. 筛选缺失年月：用左连接找出所有未被覆盖的年月，并按日期排序。

5. 合并连续年月：用变量@group标记连续的年月分组，同一个分组内的年月是连续的，最后按分组ID聚合，取每个分组的最小和最大年月作为区间起止，同时设置Subject为N/A。

测试结果验证 #

对于你提供的样例输入：

运行上述SQL后，会得到预期输出：

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