这是查询:
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()
表达式要简单得多,现在星期几在联接中。我还添加了获取默认值的酒店条件-这也应该在其他查询中。
请记住,您必须将所有日期与组合在一起放入初始子查询中union all
。