mysql计算 总价格,如何计算5天的总价格（mysql）酒店预订系统

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

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

look at this table

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)

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

解决方案

Here is the query:

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 = and

def.dayofweek = dayname(dates.thedate)

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.

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