如何用SQL实现跨月酒店预订金额按入住月份拆分生成对应行

该需求完全可以通过SQL实现，核心逻辑是通过日期序列将跨月预订拆分为按月分段记录，再按各月实际入住晚数分摊总金额，具体实现方案如下：

核心实现步骤 #

• 数据格式预处理：将源表中字符串格式的入住起止日期转换为SQL可运算的日期类型，同时将逗号分隔的金额值转换为数值类型
• 生成连续日期序列：覆盖所有预订的入住时间范围，可通过递归CTE（支持MySQL 8.0+/PostgreSQL/SQL Server等主流数据库）或提前构建的辅助日期表实现
• 关联拆分预订记录：将每条预订记录与日期序列关联，筛选出入住周期内的所有日期，按所属年月分组统计单月入住晚数
• 金额分摊计算：单晚均价=总金额/总入住晚数，单月分摊金额=单晚均价*当月入住晚数，可按需调整四舍五入精度
• 结果补全输出：按需求格式化输出对应月份、分段入住起止日期、分摊金额等字段，补全预订日期等业务字段

参考实现代码（MySQL 8.0+ 版本） #

WITH RECURSIVE date_series AS (
    -- 生成所有预订覆盖的连续日期序列
    SELECT MIN(STR_TO_DATE(`Start Date`, '%d/%m/%Y')) AS dt FROM bookings
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_series 
    WHERE dt < (SELECT MAX(STR_TO_DATE(`End date`, '%d/%m/%Y')) FROM bookings)
)
SELECT 
    b.`name`,
    b.`booking code`,
    b.`Nation`,
    b.`Adults`,
    b.`Children`,
    MONTHNAME(MIN(d.dt)) AS `month`,
    DATE_FORMAT(MIN(d.dt), '%d/%m/%Y') AS `Start Date`,
    DATE_FORMAT(
        LEAST(DATE_ADD(MAX(d.dt), INTERVAL 1 DAY), STR_TO_DATE(b.`End date`, '%d/%m/%Y')),
        '%d/%m/%Y'
    ) AS `End date`,
    COUNT(d.dt) AS `N° nights`,
    ROUND(
        (REPLACE(b.`Importo Netto (€)`, ',', '.') + 0) / b.`N° nights` * COUNT(d.dt),
        0
    ) AS `Importo Netto (€)`,
    -- 请替换为实际业务中预订日期的获取逻辑，源表未提供该字段
    b.`booking date`
FROM bookings b
INNER JOIN date_series d
    ON d.dt >= STR_TO_DATE(b.`Start Date`, '%d/%m/%Y')
    AND d.dt < STR_TO_DATE(b.`End date`, '%d/%m/%Y')
GROUP BY 
    b.`name`, b.`booking code`, b.`Nation`, b.`Adults`, b.`Children`,
    b.`N° nights`, b.`Importo Netto (€)`, b.`booking date`,
    YEAR(d.dt), MONTH(d.dt)
ORDER BY b.`booking code`, MIN(d.dt);

适配调整说明 #

• PostgreSQL：将STR_TO_DATE替换为TO_DATE，DATE_FORMAT替换为TO_CHAR，日期间隔写法调整为INTERVAL '1 day'
• SQL Server：将STR_TO_DATE替换为CONVERT，DATE_FORMAT替换为FORMAT，递归CTE语法做对应适配
• 低版本MySQL：提前构建一张存储所有日期的辅助表date_dim替换递归CTE即可
• 使用前请先过滤总入住晚数为0的异常数据，避免出现除以0的计算错误

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