我正在构建一个预订风格的系统。 房间有年度标准价格(这是我的问题与其他类似问题不同的地方,因为其他房间有独特的范围),然后它们也有一年内的日期范围,价格不同。我希望在 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
我会使用日期表:一个由一列(date
)组成的表(datelist
),包含前n年和未来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 查询中。
Use MySQL CASE 语句。
例如
SELECT Columnname,
sum(CASE
WHEN RangeName LIKE '%Standard%'
THEN do something END) as AliasName
FROM YourTable
我以前处理此类问题的方法是构建一个"日期"表。
1/1/1990 .. 1/1/2050 (你可以一蹴而就,但为了几 Mb 的存储空间,"真实"表的性能提升值得设置一次。
例如。
|Date ID| Date |
|---------------------|
|0000001| 2015-01-01 |
|0000002| 2015-01-02 |
然后,您可以基于此构建视图。 如:
Select
[date id],
if(range_name='standard',rate,0) standardPrices,
if(range_name='Summer',rate,0) summerPrices,
if(range_name='Winter',rate,0) winterPrices
from
date_table left join room_rates
on date_table.date between start_date and end_date
where room = '46' and date_table.date between '2015-07-15' and '2015-07-25'
给你一个像
date |standardPrices|summerPrices|winterPrices|
------------------------------------------------------
2015-07-15 | 100 | 0 | 0 |
2015-07-16 | 100 | 0 | 0 |
2015-07-17 | 100 | 0 | 0 |
2015-07-18 | 100 | 0 | 0 |
2015-07-19 | 100 | 0 | 0 |
2015-07-20 | 100 | 0 | 0 |
2015-07-20 | 0 | 150 | 0 |
2015-07-21 | 100 | 0 | 0 |
2015-07-21 | 0 | 150 | 0 |
如果你 THEN 按"日期"对这个视图进行分组;你可以反过来使用它来运行查询以聚合(再次使用'if (summerPrices> 0, summerPrices, standardPrices)
不是最优雅的解决方案,但它有效,并且速度快如闪电,允许您使用连接而不是在查询中循环,我更喜欢。