日期区间内包含缺失日期、月份的统计SQL实现需求

Hey，我明白你的需求——你需要统计指定区间内所有日期/月份，哪怕那天/那个月没有数据也要显示0，对吧？原SQL的问题在于它只会返回cupons表中有记录的日期/月份，那些空的日期会被漏掉。下面分两种情况给你解决方法：

你的原SQL只能返回有数据的日期，因为GROUP BY是基于表中存在的记录分组的。要包含所有日期，我们需要先生成一个覆盖整个区间的日期序列，再和统计结果做左连接。

方案1：MySQL 8.0+ 用递归CTE生成日期序列（推荐） #

WITH RECURSIVE date_range AS (
    SELECT '2017-02-02' AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < '2018-05-04'
)
SELECT 
    IFNULL(c.num, 0) AS num,
    DATE_FORMAT(d.date_val, '%d/%m/%Y') AS data
FROM date_range d
LEFT JOIN (
    SELECT 
        COUNT(*) AS num,
        DATE_FORMAT(c.dataCupo, '%Y-%m-%d') AS date_key
    FROM cupons c
    WHERE 
        c.dataCupo BETWEEN '2017-02-02' AND '2018-05-04'
        AND c.proveidor != 'VINCULADO'
        AND c.empresa = 1
    GROUP BY date_key
) c ON d.date_val = c.date_key
ORDER BY d.date_val;

说明：

• 递归CTE date_range会生成从2017-02-02到2018-05-04的每一天；
• 子查询先统计每个有数据日期的记录数，用%Y-%m-%d作为日期键来匹配；
• 左连接确保所有日期都出现在结果中，没有数据的日期num会显示0；
• 最后按日期排序保证结果顺序正确。

方案2：MySQL 5.x 用数字表生成日期序列 #

如果你的MySQL版本不支持递归CTE，可以用数字表来生成日期：

SELECT 
    IFNULL(c.num, 0) AS num,
    DATE_FORMAT(d.date_val, '%d/%m/%Y') AS data
FROM (
    SELECT 
        DATE_ADD('2017-02-02', INTERVAL n DAY) AS date_val
    FROM (
        SELECT a.N + b.N * 10 + c.N * 100 AS n
        FROM 
            (SELECT 0 AS N 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) a,
            (SELECT 0 AS N 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) b,
            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) c
        WHERE a.N + b.N * 10 + c.N * 100 <= DATEDIFF('2018-05-04', '2017-02-02')
    ) nums
) d
LEFT JOIN (
    SELECT 
        COUNT(*) AS num,
        DATE_FORMAT(c.dataCupo, '%Y-%m-%d') AS date_key
    FROM cupons c
    WHERE 
        c.dataCupo BETWEEN '2017-02-02' AND '2018-05-04'
        AND c.proveidor != 'VINCULADO'
        AND c.empresa = 1
    GROUP BY date_key
) c ON d.date_val = c.date_key
ORDER BY d.date_val;

和日期统计的思路一样，先生成所有月份的序列，再左连接统计结果，确保没有数据的月份也能显示0。

方案1：MySQL 8.0+ 用递归CTE生成月份序列 #

WITH RECURSIVE month_range AS (
    SELECT DATE_FORMAT('2017-02-02', '%Y-%m-01') AS month_val
    UNION ALL
    SELECT DATE_ADD(month_val, INTERVAL 1 MONTH)
    FROM month_range
    WHERE month_val <= DATE_FORMAT('2018-05-04', '%Y-%m-01')
)
SELECT 
    IFNULL(c.num, 0) AS num,
    DATE_FORMAT(m.month_val, '%m/%Y') AS data
FROM month_range m
LEFT JOIN (
    SELECT 
        COUNT(*) AS num,
        DATE_FORMAT(c.dataCupo, '%Y-%m') AS month_key
    FROM cupons c
    WHERE 
        c.dataCupo BETWEEN '2017-02-02' AND '2018-05-04'
        AND c.proveidor != 'VINCULADO'
        AND c.empresa = 1
    GROUP BY month_key
) c ON DATE_FORMAT(m.month_val, '%Y-%m') = c.month_key
ORDER BY m.month_val;

说明：

• month_range生成区间内每个月的第一天（比如2017-02-01、2017-03-01...2018-05-01）；
• 子查询统计每个有数据月份的记录数，用%Y-%m作为月份键匹配；
• 左连接保证所有月份都被包含，无数据的月份num为0。

方案2：MySQL 5.x 用数字表生成月份序列 #

SELECT 
    IFNULL(c.num, 0) AS num,
    DATE_FORMAT(m.month_val, '%m/%Y') AS data
FROM (
    SELECT 
        DATE_ADD(DATE_FORMAT('2017-02-02', '%Y-%m-01'), INTERVAL n MONTH) AS month_val
    FROM (
        SELECT a.N + b.N * 10 AS n
        FROM 
            (SELECT 0 AS N 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) a,
            (SELECT 0 AS N UNION ALL SELECT 1) b
        WHERE a.N + b.N * 10 <= PERIOD_DIFF(DATE_FORMAT('2018-05-04', '%Y%m'), DATE_FORMAT('2017-02-02', '%Y%m'))
    ) nums
) m
LEFT JOIN (
    SELECT 
        COUNT(*) AS num,
        DATE_FORMAT(c.dataCupo, '%Y-%m') AS month_key
    FROM cupons c
    WHERE 
        c.dataCupo BETWEEN '2017-02-02' AND '2018-05-04'
        AND c.proveidor != 'VINCULADO'
        AND c.empresa = 1
    GROUP BY month_key
) c ON DATE_FORMAT(m.month_val, '%Y-%m') = c.month_key
ORDER BY m.month_val;

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