SQL如何基于跨月check-in、check-out日期统计各id每月入住晚数

前提说明 #

• 以下SQL兼容MySQL 8.0+、PostgreSQL等支持递归CTE的主流关系型数据库
• 计算规则：跨月场景下每月入住晚数 = 「当月最后一天/离店日期的较小值」 减去 「当月首日/入住日期的较大值」，和需求示例的计算逻辑完全对齐

完整实现代码 #

WITH RECURSIVE date_range AS (
    -- 递归生成数据集覆盖的所有年月首日序列
    SELECT MIN(DATE_FORMAT(`check-in`, '%Y-%m-01')) AS month_start
    FROM hotel_records
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM date_range
    WHERE month_start < (SELECT MAX(DATE_FORMAT(checkout, '%Y-%m-01')) FROM hotel_records)
)
SELECT 
    hr.id,
    CONCAT(MONTH(dr.month_start), '月') AS 月份,
    -- 补前导0对齐需求输出格式
    LPAD(
        DATEDIFF(
            LEAST(hr.checkout, LAST_DAY(dr.month_start)),
            GREATEST(hr.`check-in`, dr.month_start)
        ),
    2, '0') AS 入住晚数
FROM hotel_records hr
-- 关联入住记录覆盖到的所有月份
JOIN date_range dr 
    ON dr.month_start <= DATE_SUB(hr.checkout, INTERVAL 1 DAY)
    AND LAST_DAY(dr.month_start) >= hr.`check-in`
ORDER BY hr.id, dr.month_start;

逻辑说明 #

• 递归CTE date_range 自动生成数据集覆盖的所有年月首日，无需手动维护日期维度表
• 关联条件过滤掉和入住记录无交集的月份，避免生成无效数据行
• LEAST、GREATEST函数自动适配跨月场景的起止日期计算，直接得出当月实际入住晚数
• LPAD函数自动补前导0，和需求要求的两位数输出格式对齐

低版本兼容方案 #

若使用不支持递归CTE的数据库版本（如MySQL 5.x），可预先构建一张包含连续年月首日的日期维度表，替换上述SQL中的date_range递归部分即可，后续计算逻辑完全不变。

样例验证 #

执行上述SQL后，你提供的测试数据集输出结果和期望完全一致：

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