如何计算5天的总价格(mysql)酒店预订系统 [英] how can calculate the total price for 5 days (mysql ) hotel booking system

问题描述

我有这张桌子来查询不同季节(高，低)季节的房间价格

I have this table for the price of rooms with diffrent season ( high , low ) season

如何获得不同天数和不同季节的5天总价格.

how can get total price for 5 days with diffrent days and diffrent season ..

看看这张桌子

这是我的命令(mysql) 我想获取从 2013年4月7日到 2013年11月7日的10晚的总价格

this is my command ( mysql ) I want to get the total price for 10 nights from 04-07-2013 to 11-07-2013

select sum(
case when dayname('2013-01-07') = 'Monday' then coalesce(prices.room_price , def.room_price) 
 when dayname('2013-01-07') = 'Sunday' then coalesce(prices.room_price , def.room_price) 

)as TotalPrice 
from (select strtodate('2013-01-07' , '%Y-%m-%d') as thedate 
select strtodate('2013-01-08' , '%Y-%m-%d') as thedate ) dates left outer join ts_room_prices prices
on dates.thedate between prices.season_start and prices.season_end cross join ts_room prices def on def.season_name = 'default'

---

---

添加

我写了这个命令，但仍然没用

---

---

addition

I wrote this command but still dosen't work

select sum(coalesce(prices.room_price , def.room_price) ) as TotalPrice
from (select strtodate('2013-01-07' , '%Y-%m-%d') as thedate union all
      select strtodate('2013-01-08' , '%Y-%m-%d') as thedate
     ) dates left outer join
     ts_room_prices prices
     on dates.thedate between prices.season_start and prices.season_end and
        dayname(dates.thedate) = prices.dayofweek join ts_room_prices def
    on def.season_name = 'default' and
       def.hotel = 3233 and
       def.dayofweek = dayname(dates.thedate)

错误:#1305-功能saudihot_saudihotels.strtodate不存在

Error: #1305 - FUNCTION saudihot_saudihotels.strtodate does not exist

推荐答案

以下是查询:

select sum(coalesce(prices.room_price , def.room_price) ) as TotalPrice
from (select strtodate('2013-01-07' , '%Y-%m-%d') as thedate union all
      select strtodate('2013-01-08' , '%Y-%m-%d') as thedate
     ) dates left outer join
     ts_room_prices prices
     on dates.thedate between prices.season_start and prices.season_end and
        dayname(dates.thedate) = prices.dayofweek join ts_room prices def
    on def.season_name = 'default' and
       def.hotel = <whatever the hotel is> and
       def.dayofweek = dayname(dates.thedate)

请注意，sum()表达式要简单得多，现在星期几在联接中.我还添加了获取默认值的酒店条件-这也应该在其他查询中.

Notice that the sum() expression is much simpler and the day of week is now in the joins. I also added the hotel condition for getting the default values -- this should be in the other query as well.

请记住，必须将所有日期与union all一起放在初始子查询中.

Remember that you have to put all the dates in the initial subquery, combined with union all.

这篇关于如何计算5天的总价格(mysql)酒店预订系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！