给定起止日期统计跨月天数并生成对应报表的SQL查询求助

• 首先构造连续的月份维度序列，覆盖所有业务记录的开始日期到结束日期涉及的全部月份
• 将原始业务表和月份维度序列关联，筛选出月份和业务记录时间范围存在重叠的关联结果
• 计算单条业务记录在对应月份的有效天数：取当月最后一天和记录结束日期的较小值，减去当月第一天和记录开始日期的较大值，结果加1即为当月有效天数
• 最后按项目ID、统计月份分组聚合，即可得到目标报表

以下代码适配MySQL 8.0+、PostgreSQL、Hive等支持递归CTE的SQL环境，默认你的原始业务表名为t_project，包含project_id（项目ID）、start_date（开始日期）、end_date（结束日期）三个核心字段，你可以根据实际表结构调整字段名和表名：

WITH RECURSIVE date_range AS (
    -- 取所有记录最小开始日期所在月的第一天作为序列起点
    SELECT MIN(DATE_FORMAT(start_date, '%Y-%m-01')) AS month_start
    FROM t_project
    UNION ALL
    -- 递归生成后续月份的第一天，直到超过所有记录的最大结束日期
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM date_range
    WHERE month_start <= (SELECT MAX(end_date) FROM t_project)
)
SELECT 
    p.project_id,
    DATE_FORMAT(d.month_start, '%Y-%m') AS stat_month,
    -- 计算当月有效天数
    DATEDIFF(
        LEAST(p.end_date, LAST_DAY(d.month_start)),
        GREATEST(p.start_date, d.month_start)
    ) + 1 AS day_count
FROM t_project p
INNER JOIN date_range d
-- 关联条件：当前月份和项目时间范围存在重叠
ON d.month_start <= p.end_date 
AND LAST_DAY(d.month_start) >= p.start_date
ORDER BY p.project_id, stat_month;

如果你使用的是不支持递归CTE的低版本MySQL，可以预先构建一张存储所有连续月份的维度表，替换上述代码中的date_range递归CTE部分，关联计算逻辑保持不变即可。

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