mysql在日期范围和列总和之间选择日期

我有以下表格：

假设第一张桌子代表顾客预订的酒店.

+------------+-----------+-----------+--------------+----------------+
| booking_id | client_id | room_type | arrival_date | departure_date |
+------------+-----------+-----------+--------------+----------------+
| 1 | 1 | 1 | 2016-05-30 | 2016-06-03 |
+------------+-----------+-----------+--------------+----------------+


第二张表包含不同类型房间的价格.价格在不同的时间段会有所不同.

+---------------+-----------+------------+------------+-------+
| room_price_id | room_type | start_date | end_date | price |
+---------------+-----------+------------+------------+-------+
| 1 | 1 | 2016-03-01 | 2016-05-31 | 150 |
| 2 | 1 | 2016-06-01 | 2016-08-31 | 200 |
+---------------+-----------+------------+------------+-------+


我的问题是：如果整个预订期间的价格都不一样,如何计算预订的总费用？ (在我的情况下,总费用为700)

我阅读了以下链接：MySQL: Select all dates between date range and get table data matching dates和Select data from date range between two dates,但是在如何解决我的问题上我不明白,因为价格表中的条目仅包含日期范围,而不像第一个链接那样包含明确的日期.

解决方法:

您的问题的诀窍在于制定预订表和价格表之间的联接条件.一旦解决了这个问题,简单的聚合就可以为您提供所需的结果.只要给定价格范围内的任何点都在到达或离开日期的范围内,则加入条件应为true.

然后,为了计算天数,我们使用到达日期或开始日期中的较大者,以及出发日期或结束日期中的较小者.

SELECT
b.booking_id,
SUM((DATEDIFF(LEAST(b.departure_date, p.end_date),
GREATEST(b.arrival_date, p.start_date)) + 1) * p.price) AS total
FROM booking b
INNER JOIN prices p
ON b.room_type = p.room_type AND
(p.start_date BETWEEN b.arrival_date AND b.departure_date OR
p.end_date BETWEEN b.arrival_date AND b.departure_date)
GROUP BY b.booking_id;


您的样本数据的输出为900.这是正确的,因为：

2 days x 150 = 300
3 days x 200 = 600
total = 900


演示在这里：

SQLFiddle