MySQL 5.5：查询同表两个日期字段间的当月所有日期

现有一张包含Id、Name（姓名）、Startdate（开始日期）、Enddate（结束日期）字段的数据表，需要查询每条记录的Startdate与Enddate区间内属于当前月份的所有日期：

• 若日期区间跨当前月份，仅返回区间内属于当前月的部分日期（比如Alden的区间是2024-01-20至2024-02-20，当前月份为1月时，仅返回1月20日到1月31日的所有日期）
• 若日期区间完全在当前月份内，则返回区间的全部日期（比如Justin的区间是2024-01-01至2024-01-10，当前月份为1月时，返回该区间的所有日期）

示例数据表 #

预期输出结果（当前月份为1月时） #

解决方案 #

核心思路是先生成当前月份的完整日期序列，再与原数据表关联，筛选出落在每条记录日期区间内的日期。以下是主流数据库的实现写法：

1. MySQL 写法 #

WITH RECURSIVE dates AS (
    -- 生成当前月份第一天
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS date
    UNION ALL
    -- 递归生成当月后续日期
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM dates
    WHERE date < LAST_DAY(CURDATE())
)
SELECT 
    t.Name AS 姓名,
    DATE_FORMAT(d.date, '%d-%m-%Y') AS 日期
FROM 
    your_table t
JOIN 
    dates d ON d.date BETWEEN STR_TO_DATE(t.Startdate, '%d-%m-%Y') AND STR_TO_DATE(t.Enddate, '%d-%m-%Y')
WHERE 
    MONTH(d.date) = MONTH(CURDATE()) 
    AND YEAR(d.date) = YEAR(CURDATE())
ORDER BY 
    姓名, 日期;

替换your_table为实际表名，若日期字段已是DATE类型，可去掉STR_TO_DATE转换。

2. SQL Server 写法 #

WITH dates AS (
    -- 生成当前月份第一天
    SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS date
    UNION ALL
    -- 递归生成当月后续日期
    SELECT DATEADD(DAY, 1, date)
    FROM dates
    WHERE date < EOMONTH(GETDATE())
)
SELECT 
    t.Name AS 姓名,
    FORMAT(d.date, 'dd-MM-yyyy') AS 日期
FROM 
    your_table t
JOIN 
    dates d ON d.date BETWEEN CONVERT(DATE, t.Startdate, 105) AND CONVERT(DATE, t.Enddate, 105)
ORDER BY 
    姓名, 日期
OPTION (MAXRECURSION 31);

105是dd-MM-yyyy格式的转换代码，需与日期字段格式匹配；替换your_table为实际表名。

3. PostgreSQL 写法 #

WITH dates AS (
    -- 生成当前月份的所有日期
    SELECT generate_series(
        DATE_TRUNC('month', CURRENT_DATE)::DATE,
        DATE_TRUNC('month', CURRENT_DATE)::DATE + INTERVAL '1 month' - INTERVAL '1 day',
        INTERVAL '1 day'
    )::DATE AS date
)
SELECT 
    t.Name AS 姓名,
    TO_CHAR(d.date, 'DD-MM-YYYY') AS 日期
FROM 
    your_table t
JOIN 
    dates d ON d.date BETWEEN TO_DATE(t.Startdate, 'DD-MM-YYYY') AND TO_DATE(t.Enddate, 'DD-MM-YYYY')
ORDER BY 
    姓名, 日期;

替换your_table为实际表名，日期格式符需与字段格式匹配。

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