如何用CTE拆分跨月日期记录并计算各月份天数？

假设你的现有查询返回名称、开始日期、结束日期这三列，下面用递归CTE把它集成进去，实现按月份拆分并计算当月天数的需求：

WITH base_data AS (
    -- 把你的现有查询放到这里，替换下面的示例查询
    SELECT 'Test1' AS 名称, '2023-12-01'::DATE AS 开始日期, '2024-01-15'::DATE AS 结束日期
),
recursive_dates AS (
    -- 初始化递归：取每条记录的第一个月份区间
    SELECT
        名称,
        开始日期,
        结束日期,
        -- 当月的第一天
        DATE_TRUNC('month', 开始日期)::DATE AS month_start,
        -- 当月的最后一天，或者原结束日期（如果当月就是结束月）
        LEAST(DATE_TRUNC('month', 开始日期) + INTERVAL '1 month - 1 day', 结束日期)::DATE AS month_end
    FROM base_data
    UNION ALL
    -- 递归生成后续月份的区间
    SELECT
        名称,
        开始日期,
        结束日期,
        month_start + INTERVAL '1 month'::DATE,
        LEAST(month_start + INTERVAL '2 months - 1 day', 结束日期)::DATE
    FROM recursive_dates
    WHERE month_end < 结束日期
)
-- 最终查询：计算天数、月份名称和年份
SELECT
    名称,
    开始日期,
    结束日期,
    -- 生成英文月份缩写，不同数据库函数有差异，比如MySQL用DATE_FORMAT(month_start, '%b')
    TO_CHAR(month_start, 'Mon') AS 月份,
    EXTRACT(YEAR FROM month_start) AS 年份,
    -- 计算当月天数：month_end - month_start + 1
    (month_end - month_start + 1) AS 天数
FROM recursive_dates
ORDER BY 名称, month_start;

关键说明： #

• base_data：把你的现有查询完全替换掉示例里的SELECT语句就行，保证输出名称、开始日期、结束日期三列即可。
• 递归逻辑：从每条记录的起始月份开始，逐月生成新的区间，直到覆盖原结束日期所在的月份。
• 天数计算：用当月的实际结束日（要么是月末，要么是原记录的结束日）减去当月起始日再加1，得到当月实际天数。
• 月份缩写：不同数据库的函数有差异：
  • PostgreSQL用TO_CHAR(month_start, 'Mon')
  • MySQL用DATE_FORMAT(month_start, '%b')
  • SQL Server用DATENAME(month, month_start)取全称，或者自定义缩写映射

示例输入输出 #

输入（现有查询结果）：

输出：

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