Hotel_id Room_id Room_type开始日期结束日期价格----------------------------------------------------------------13 2标准2012-08-01 2012-08-15 700013 2标准2012-08-16 2012-08-31 750013 2标准2012-09-01 2012-09-30 600013 3豪华2012-08-01 2012-08-15 900013 3豪华2012-08-16 2012-08-31 1000013 3豪华2012-09-01 2012-09-30 9500
嗨,这是我表格的结构和数据。
我需要为酒店预订创建一个mysql查询,它将在数据库中匹配用户输入的数据:
- 他们想要签入和签出的日期
- 房间类型
例如:
如果用户根据这些日期选择带豪华客房的酒店(2012-08-30至2012-09-04)8月30日和31日的总费用为(10000*2),9月1日、2日和3日的总成本为(9500*3)(不包括第四个结账日)这意味着总价格将是20000+28500=48500
所以查询应该根据Hotel_id、Room_id、Start_date、End_date和price 过滤总价
感谢
使用此解决方案:
SELECT SUM(
CASE WHEN a.Start_date = b.min_sd AND a.Start_date <> b.max_sd THEN
(DATEDIFF(a.End_date, '2012-08-30')+1) * a.Price
WHEN a.Start_date = b.max_sd AND a.Start_date <> b.min_sd THEN
DATEDIFF('2012-09-04', a.Start_date) * a.Price
WHEN (a.Start_date,a.Start_date) IN ((b.min_sd,b.max_sd)) THEN
(DATEDIFF('2012-09-04', '2012-08-30')+1) * a.Price
WHEN a.Start_date NOT IN (b.min_sd, b.max_sd) THEN
(DATEDIFF(a.End_date, a.Start_date)+1) * a.Price
END
) AS totalprice
FROM rooms a
CROSS JOIN (
SELECT MIN(Start_date) AS min_sd,
MAX(Start_date) AS max_sd
FROM rooms
WHERE Room_type = 'luxury' AND
End_date >= '2012-08-30' AND
Start_date <= '2012-09-04'
) b
WHERE a.Room_type = 'luxury' AND
a.End_date >= '2012-08-30' AND
a.Start_date <= '2012-09-04'
将2012-08-30
和2012-09-04
的出现分别替换为您输入的开始日期和结束日期。
这将考虑到开始日期和结束日期在同一个月以及跨越多个月。
SQLFiddle演示
您可以使用MySQL的BETWEEN ... AND ...
操作员查找所需预订的日期范围(记得在指定结账后休息一天约会,就像你说的,没有过夜),然后按房间分组结果价格乘以夜数的SUM()
(可以使用MySQL的LEAST()
和GREATEST()
函数):
SELECT Room_id,
SUM(Price * (1 + DATEDIFF(
LEAST(End_date, '2012-09-04' - INTERVAL 1 DAY),
GREATEST(Start_date, '2012-08-30')
))) AS Total
FROM mytable
WHERE Room_type = 'luxury' AND (
'2012-09-04' - INTERVAL 1 DAY
BETWEEN Start_date AND End_date
OR '2012-08-30' BETWEEN Start_date AND End_date
)
GROUP BY Room_id
请在sqlfidde上查看。
试试这个:
set @Hotel_id :=13;
set @Room_id :=3;
set @Start_date :='2012-08-30' ;
set @End_date :='2012-09-04';
select sum(b.TotalPrice-b.deductions) as total_cost from
( select a.Price,a.StartDate,a.EndDate,price*(DATEDIFF(a.EndDate,a.StartDate)+1) as TotalPrice
,case when a.EndDate=@End_date then a.Price else 0 end as deductions
from
(select price,case when @Start_date>=Start_date then @Start_date else Start_date end as StartDate
,case when @End_date<=End_date then @End_date else End_date end as EndDate
from h_booking h1
where Hotel_id=@Hotel_id
and Room_id=@Room_id
and (@Start_date between Start_date and End_date or @End_date between Start_date and End_date ))a )b
添加到@egyal解决方案以便如果存在超过两个月的条目,则不会忽略某些条目;此更改将在之间(例如在90天的时间段内)未检查的行包括在总数中。例如,在问题中给出的表格中,当查询范围2012-08-01-2012--09-01时,日期为2012-08-16 2012-08-31的价格线不符合中间条件,因此不包括在总数中。这个小附录将把这些行加到总数中。虽然很老了,但这里给出的答案对我帮助很大。感谢大家提供的解决方案。
SELECT Room_id,
SUM(price * (1 + DATEDIFF( LEAST(End_date, '2023-09-23' - INTERVAL 1 DAY), GREATEST(Start_date, '2023-06-25') ))) AS Total
FROM mytable
WHERE Room_type = 'luxury'
AND (( '2023-09-23' - INTERVAL 1 DAY BETWEEN Start_date AND End_date)
OR ('2023-06-25' BETWEEN Start_date AND End_date )
OR (End_date < '2023-09-23' AND Start_date > '2023-06-25')) GROUP BY Room_id