如何返回当前月份与项目结束月份之间的所有月份日期？

现有一张包含多个项目结束日期的数据表，需要返回当前月份至各项目结束日期之间所有月份的日期。返回日期的日部分无关紧要，只需逐月递减结束日期直至当前月份即可。

示例输入表 #

期望输出 #

解决方案 #

1. MySQL/MariaDB #

利用递归CTE生成日期序列，再与原表关联：

WITH RECURSIVE date_series AS (
    SELECT 
        Project,
        `End Date` AS end_date,
        `End Date` AS preceding_date
    FROM your_table
    UNION ALL
    SELECT 
        ds.Project,
        ds.end_date,
        DATE_SUB(ds.preceding_date, INTERVAL 1 MONTH) AS preceding_date
    FROM date_series ds
    WHERE DATE_SUB(ds.preceding_date, INTERVAL 1 MONTH) >= DATE_FORMAT(NOW(), '%Y-%m-01')
)
SELECT 
    Project AS `项目（Project）`,
    end_date AS `结束日期（End Date）`,
    preceding_date AS `前置日期（Preceding Dates）`
FROM date_series
ORDER BY Project, preceding_date;

2. PostgreSQL #

结合递归CTE与日期截断函数处理：

WITH RECURSIVE date_series AS (
    SELECT 
        "Project",
        "End Date" AS end_date,
        "End Date" AS preceding_date
    FROM your_table
    UNION ALL
    SELECT 
        ds."Project",
        ds.end_date,
        ds.preceding_date - INTERVAL '1 month' AS preceding_date
    FROM date_series ds
    WHERE ds.preceding_date - INTERVAL '1 month' >= date_trunc('month', CURRENT_DATE)
)
SELECT 
    "Project" AS "项目（Project）",
    end_date AS "结束日期（End Date）",
    preceding_date::DATE AS "前置日期（Preceding Dates）"
FROM date_series
ORDER BY "Project", preceding_date;

3. SQL Server #

使用递归CTE与日期增减函数，跨度大时需开启最大递归限制：

WITH date_series AS (
    SELECT 
        Project,
        [End Date] AS end_date,
        [End Date] AS preceding_date
    FROM your_table
    UNION ALL
    SELECT 
        ds.Project,
        ds.end_date,
        DATEADD(MONTH, -1, ds.preceding_date) AS preceding_date
    FROM date_series ds
    WHERE DATEADD(MONTH, -1, ds.preceding_date) >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
)
SELECT 
    Project AS [项目（Project）],
    end_date AS [结束日期（End Date）],
    preceding_date AS [前置日期（Preceding Dates）]
FROM date_series
ORDER BY Project, preceding_date
OPTION (MAXRECURSION 0);

关键说明 #

• 递归逻辑从项目结束日期开始，逐月往前生成日期，直到当前月份的第一天。
• 若需统一前置日期为每月1号，可在生成时用DATE_FORMAT（MySQL）、date_trunc（PostgreSQL）或DATEFROMPARTS（SQL Server）替换原有日期处理逻辑。
• 代码中your_table需替换为实际数据表名。

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