php - 计算给定标准费率和季节性日期范围的价格

Question

我正在构建一个预订式系统。房间有每年的标准价格(这是我的问题与其他类似问题不同的地方，因为其他房间有独特的范围)，然后它们也有一年内的日期范围，并以不同的价格设置。我正在计算 MYSQL 中的价格，但是当用户选择特殊日期范围内的日期时，它会对该价格进行求和，同时也会对这些日期的标准价格求和，这意味着总数太高了。这有点啰嗦，所以这里是查询和示例:

SELECT SUM(Price * (1 + DATEDIFF(LEAST(End_date, '2015-07-25' - INTERVAL 1 DAY), GREATEST(Start_date, '2015-07-15')))) AS Total
FROM room_rates
WHERE roomId = '46' AND (
       '2015-07-25' - INTERVAL 1 DAY BETWEEN Start_date AND End_date 
    OR '2015-07-15'                  BETWEEN Start_date AND End_date
)

+--------+------------+------------+------------+------+
| RoomId | Range Name | Start_Date |  End_Date  | Rate |
+--------+------------+------------+------------+------+
|   46   |  Standard  | 2015-01-01 | 2015-12-31 | 100  |
+--------+------------+------------+------------+------+
|   46   |   Summer   | 2015-07-20 | 2015-08-31 | 150  |
+--------+------------+------------+------------+------+
|   46   |  Christmas | 2015-12-18 | 2015-12-31 | 180  |
+--------+------------+------------+------------+------+

如果用户选择 2015-07-15 到 2015-07-25(如我的查询中的示例所示)，我想要的计算如下:

• 2015-07-15 ... 2015-07-19 标准房价(每晚 100 美元)
• 2015-07-20 ... 2015-07-24 夏季房价(每晚 150 美元)

总计应为 1250。但是由于标准费率的日期介于一年中的第一天和最后一天之间，因此查询还包括该价格以及夏季日期期间该范围内的费率总和，这意味着我的所有 10 次约会均按标准价格收费，再加上 5 晚夏季价格，总计 1750 美元。

所以我的问题是，如果没有可用的替代方案，如何修改查询以仅使用标准费率？标准费率始终称为“标准”，因此我可以轻松识别它们，我只是不知道要进行哪些更改!

编辑

我应该补充一点，我希望在 PHP 中执行此操作(我正在使用 PDO)

第二次编辑另外值得注意的是，日期范围始终在一年之内，并且任何日期范围都不能重叠(除了占用全年的标准费率)

解决方案

决定改变我的方法并采用几个人建议的答案并建立一个日常数据库。这是我最后的有效查询。感谢大家的帮助和建议。

SELECT standard_rates.villaId as `villaId`,
            sum(IFNULL(custom_rates.nightly_rate_usd, standard_rates.nightly_rate_usd))
        AS `Rate`
        FROM dates

          LEFT JOIN
            villa_price_bands AS standard_rates
            ON standard_rates.Name = 'Standard'
            AND dates.date BETWEEN standard_rates.Start_Date AND standard_rates.End_Date
            AND FIND_IN_SET(standard_rates.villaId, :resultIds)

          LEFT JOIN
            villa_price_bands AS custom_rates
            ON custom_rates.Name != 'Standard'
            AND dates.date BETWEEN custom_rates.Start_Date AND custom_rates.End_Date
            AND custom_rates.villaId = standard_rates.villaId

        WHERE dates.date >= :arrDate
        AND dates.date < :deptDate
        GROUP BY villaId

Answer 1

我将使用日期表:一个由一列 (date) 组成的表 (datelist)，并包含之前和 future 的所有日期 n 年。

查询的粗略轮廓(您可能需要更正结束日期):

SELECT
    datelist.date AS Night,
    IFNULL(seasonal_rates.Range_Name, standard_rates.Range_Name) AS Season,
    IFNULL(seasonal_rates.Rate, standard_rates.Rate) AS Rate
FROM datelist
LEFT JOIN rates AS standard_rates ON standard_rates.Range_Name = 'Standard'
LEFT JOIN rates AS seasonal_rates ON datelist.date BETWEEN seasonal_rates.Start_Date AND seasonal_rates.End_Date
WHERE standard_rates.RoomId = 46
AND   seasonal_rates.RoomId = 46
AND   datelist.date BETWEEN '2015-07-15' AND '2015-07-25'

然后您可以将结果传递到 SUM/GROUP BY 查询中。