如何在含日期数据的表中查询整月日期？SQL实现方案

核心思路是先生成目标月份的完整日期序列，再通过左连接关联原有数据表，这样无匹配数据的日期会自动显示NULL。以下是主流数据库的具体实现方案：

MySQL（8.0+ 版本） #

使用递归CTE生成日期范围：

WITH RECURSIVE date_range AS (
    SELECT '2023-05-01' AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < '2023-05-31'
)
SELECT 
    dr.date_val AS record_date,
    dd.value
FROM date_range dr
LEFT JOIN date_data dd ON dr.date_val = dd.record_date
ORDER BY dr.date_val;

如果是MySQL 5.x（不支持CTE），可以用数字列表生成日期：

SELECT 
    DATE('2023-05-01') + INTERVAL (n-1) DAY AS record_date,
    dd.value
FROM (
    SELECT 1 n 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 UNION ALL SELECT 24 UNION ALL SELECT 25
    UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
    UNION ALL SELECT 31
) nums
LEFT JOIN date_data dd ON DATE('2023-05-01') + INTERVAL (nums.n-1) DAY = dd.record_date
ORDER BY record_date;

PostgreSQL #

利用内置的generate_series函数快速生成日期序列：

SELECT 
    dr.date_val::date AS record_date,
    dd.value
FROM generate_series(
    '2023-05-01'::date,
    '2023-05-31'::date,
    '1 day'::interval
) dr(date_val)
LEFT JOIN date_data dd ON dr.date_val = dd.record_date
ORDER BY dr.date_val;

SQL Server #

递归CTE生成日期范围，注意设置递归上限：

WITH date_range AS (
    SELECT CAST('2023-05-01' AS DATE) AS date_val
    UNION ALL
    SELECT DATEADD(DAY, 1, date_val)
    FROM date_range
    WHERE date_val < '2023-05-31'
)
SELECT 
    dr.date_val AS record_date,
    dd.value
FROM date_range dr
LEFT JOIN date_data dd ON dr.date_val = dd.record_date
ORDER BY dr.date_val
OPTION (MAXRECURSION 31);

注意：请根据实际表名和字段名，替换示例中的date_data（表名）、record_date（日期字段）、value（值字段）。

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