跨月份预订的公寓月度报表价格分月求和计算问题

核心问题分析 #

你的现有SQL直接按入住月份筛选订单并统计全额费用，无法处理跨月订单的拆分需求。要实现分月统计，需要计算每个订单在目标月份内的实际住宿天数，再乘以单价得到该订单对应当月的费用。

关键解决方案 #

1. 日期格式转换：由于你的日期存储为dd.m.yyyy字符串，需先用STR_TO_DATE转换为MySQL可识别的日期类型，否则日期函数无法正常工作。
2. 计算月度重叠住宿天数：对每个订单，取「入住日期与当月第一天的较大值」作为当月住宿起始，「退房日期与当月最后一天次日的较小值」作为当月住宿结束，两者的日期差即为当月住宿天数（因为退房日期当天不计入住宿）。
3. 动态获取上月的年月：避免直接用date('m')-1导致1月时出现0的错误，改用strtotime('-1 month')获取完整的上年月格式。

完整代码实现 #

PHP部分（获取上月年月）

// 获取上月的年月（格式：YYYY-MM），自动处理跨年情况（如1月的上月是去年12月）
$last_month = date('Y-m', strtotime('-1 month'));
list($target_year, $target_month) = explode('-', $last_month);

SQL部分（统计上月费用）

SELECT 
    SUM(
        CASE
            -- 订单完全在目标月份之后，无费用
            WHEN STR_TO_DATE(arrival_date, '%d.%m.%Y') > LAST_DAY(CONCAT(:target_year, '-', :target_month, '-01')) THEN 0
            -- 订单完全在目标月份之前，无费用
            WHEN STR_TO_DATE(departure_date, '%d.%m.%Y') < CONCAT(:target_year, '-', :target_month, '-01') THEN 0
            -- 计算目标月份内的住宿天数，再乘以单价
            ELSE DATEDIFF(
                LEAST(STR_TO_DATE(departure_date, '%d.%m.%Y'), DATE_ADD(LAST_DAY(CONCAT(:target_year, '-', :target_month, '-01')), INTERVAL 1 DAY)),
                GREATEST(STR_TO_DATE(arrival_date, '%d.%m.%Y'), CONCAT(:target_year, '-', :target_month, '-01'))
            ) * price_per_night
        END
    ) AS sum_price
FROM bookings;

代码说明 #

• 参数绑定：使用:target_year和:target_month参数（避免SQL注入），对应PHP中获取的$target_year和$target_month。
• 重叠天数计算：
  • 对示例订单arrival_date='29.1.2023'、departure_date='2.2.2023'，目标月份为2023-01时：
    • 起始日期取max('2023-01-29', '2023-01-01') = '2023-01-29'
    • 结束日期取min('2023-02-02', '2023-02-01') = '2023-02-01'
    • 天数差为DATEDIFF('2023-02-01', '2023-01-29') = 3，乘以单价50得到150，符合1月报表需求。
  • 当目标月份为2023-02时，计算得到的天数为1，费用50，符合2月报表需求。
• 边界处理：自动过滤完全不重叠的订单，避免无效计算。

注意事项 #

• 确保你的MySQL版本支持STR_TO_DATE、LAST_DAY等日期函数（MySQL 5.5及以上均支持）。
• 建议使用参数化查询替代直接拼接SQL，防止SQL注入风险。

内容的提问来源于stack exchange，提问作者Jure Virtič