mysql - mysql查询酒店空房情况(不计算总价)

搜索住宿清单和总价。

这是我的查询和输出：http://sqlfiddle.com/#!2/eaf58/43

查询：

SELECT count(candidates.accommodation_id) as total, candidates.accommodation_id, omc_accommodation_season_period.season_id,
       room_1, r1_price.price_normal AS room_1_price,
       (SELECT SUM(room1_price.price_normal)
        FROM omc_accommodation_room_pricelist room1_price
        INNER JOIN omc_accommodation_room room1
        ON room1_price.accommodation_id = room1.accommodation_id
        INNER JOIN omc_accommodation_season_period so1
        ON room1_price.accommodation_id = so1.accommodation_id
           AND room1_price.season_id = so1.season_id
        INNER JOIN omc_accommodation accommodation1
        ON room1_price.accommodation_id = accommodation1.id
        WHERE
           so1.date BETWEEN '2013-08-13' AND '2013-08-15' AND
           room1.quantity >= 3 AND room1.pax_max = 1 AND
           accommodation1.id = candidates.accommodation_id AND
           room1.id = room_1) AS room_1_sum,
       room_2,r2_price.price_normal AS room_2_price,
       omc_accommodation_season_period.date
FROM (SELECT r1.accommodation_id, r1.id AS room_1,
             r2.id AS room_2, r1.quantity AS room_1_quantity,
             r2.quantity AS room_2_quantity
      FROM
         omc_accommodation_room r1
      INNER JOIN
         omc_accommodation_room r2 ON r1.accommodation_id = r2.accommodation_id
      WHERE
          r1.quantity >= 3 AND r2.quantity >= 4 AND r1.pax_max = 1 AND r2.pax_max = 2) AS candidates
LEFT JOIN omc_accommodation_season_period
          ON candidates.accommodation_id = omc_accommodation_season_period.accommodation_id
             AND date BETWEEN '2013-08-13' AND '2013-08-15'
             AND omc_accommodation_season_period.date <> 0
LEFT JOIN omc_accommodation_room_extra_quota eq1
          ON (candidates.accommodation_id = eq1.accommodation_id
              AND room_1 = eq1.room_type_id
              AND eq1.date=omc_accommodation_season_period.date)
LEFT JOIN omc_accommodation_room_extra_quota eq2
          ON (candidates.accommodation_id = eq2.accommodation_id
              AND room_2 = eq2.room_type_id
              AND eq2.date=omc_accommodation_season_period.date)
LEFT JOIN omc_accommodation
          ON candidates.accommodation_id = omc_accommodation.id
LEFT JOIN omc_accommodation_room_pricelist r1_price
          ON (candidates.accommodation_id = r1_price.accommodation_id
              AND room_1= r1_price.room_type_id)
LEFT JOIN omc_accommodation_room_pricelist r2_price
          ON (candidates.accommodation_id = r2_price.accommodation_id
              AND room_1 = r2_price.room_type_id)
WHERE
    r1_price.season_id =  omc_accommodation_season_period.season_id
    AND
    r2_price.season_id =  omc_accommodation_season_period.season_id
GROUP BY candidates.accommodation_id, omc_accommodation_season_period.date

问题仅在于计算每个房间的总价。

帮我修好..

提前谢谢了

最佳答案

SELECT rsp1.date, rsp1.season_id, r1.hotel_id, r1.id AS room_1, rp1.price_normal AS room_1_price, SUM(rp1.price_normal) AS room_1_total, r2.id AS room_2, rp2.price_normal AS room_2_price
FROM
omc_hotel_room r1
LEFT JOIN omc_hotel_room r2 ON r1.hotel_id = r2.hotel_id
INNER JOIN omc_hotel_room_pricelist rp1 ON r1.hotel_id = rp1.hotel_id AND r1.id = rp1.room_type_id
INNER JOIN omc_hotel_room_pricelist rp2 ON r1.hotel_id = rp1.hotel_id AND r2.id = rp2.room_type_id
LEFT JOIN omc_hotel_season_period rsp1 ON r1.hotel_id = rsp1.hotel_id
WHERE
    r1.pax_max = 1 AND
    rp1.price_normal <> 0 AND
    r2.pax_max = 2 AND
    rp2.price_normal <> 0 AND
    rsp1.date BETWEEN '2013/08/13' AND '2013/08/15'
GROUP BY r1.hotel_id, rsp1.date

关于mysql - mysql查询酒店空房情况(不计算总价)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18191002/