MySQL - 查找在特定日期有空房的酒店

Question

我看过很多类似的问题，但还没能让它为我所用。我正在创建一个酒店预订系统，我希望列出在请求的预订日期至少有一个房间可用的酒店。

这是我到目前为止提出的问题

SELECT
  ac.id,
  ac.name,
  ac.link,
  ac.type,
  ac.country,
  co.name AS countryName,
  ac.state,
  st.statename,
  ac.cityid AS cityId,
  ci.name AS cityName,
  ac.addr,
  ac.featuredimage,
  ac.amenities,
  ac.starrating,
  ac.bookings_received,
  ro.roomprice,
  SUM(ro.numberofrooms) AS totalRooms,
  roomsBooked = (
  SELECT
    *,
    SUM(numberofroomsbooked) AS roomsBooked
  FROM
    bookings
  WHERE
    reservationto >= '2016-07-07' AND reservationfrom <= '2016-07-09'
    AND hotelid = ro.hotel
)
FROM
  accomodations ac
JOIN countries co ON ac.country = co.id
JOIN states st ON ac.state = st.id
JOIN city ci ON ci.id = ac.cityid
JOIN room ro ON ac.id = ro.hotel
WHERE
  ac.active = 1 AND ac.delete = 0
GROUP BY
  ac.id

我是MySQL的新手，这真的失控了。这个想法是计算酒店的房间总数，减去给定日期范围内预订的房间总数。我能得到一些帮助吗？

子查询

      SELECT
        *,
        SUM(numberofroomsbooked) AS roomsBooked
      FROM
        bookings
      WHERE
        reservationto >= '2016-07-07' AND reservationfrom <= '2016-07-09'
        AND hotelid = ro.hotel

它自己就能很好地工作，但这个不行。

Answer 1

是否尝试过此查询？您按住宿酒店分组，因此，如果您将 SUM 函数与预订的房间一起使用，它应该会返回您预订的房间总数。我没有尝试过。

我希望它能解决你的问题。

SELECT
  ac.id,
  ac.name,
  ac.link,
  ac.type,
  ac.country,
  co.name AS countryName,
  ac.state,
  st.statename,
  ac.cityid AS cityId,
  ci.name AS cityName,
  ac.addr,
  ac.featuredimage,
  ac.amenities,
  ac.starrating,
  ac.bookings_received,
  ro.roomprice,
  SUM(ro.numberofrooms) AS totalRooms,
  SUM(numberofroomsbooked) AS bookedRooms
FROM
  accomodations ac
JOIN bookings b ON ac.id = b.hotelid
JOIN countries co ON ac.country = co.id
JOIN states st ON ac.state = st.id
JOIN city ci ON ci.id = ac.cityid
JOIN room ro ON ac.id = ro.hotel
WHERE
  ac.active = 1 AND ac.delete = 0
  AND reservationto >= '2016-07-07' AND reservationfrom <= '2016-07-09'
GROUP BY
  ac.id;

Answer 2

编辑:基于上一个查询构建

SELECT
  ac.id,
  ac.name,
  ac.link,
  ac.type,
  ac.country,
  co.name AS countryName,
  ac.state,
  st.statename,
  ac.cityid AS cityId,
  ci.name AS cityName,
  ac.addr,
  ac.featuredimage,
  ac.amenities,
  ac.starrating,
  ac.bookings_received,
  ro.roomprice,
  ro.totalRooms
FROM
  accomodations ac
JOIN countries co ON ac.country = co.id
JOIN states st ON ac.state = st.id
JOIN city ci ON ci.id = ac.cityid
JOIN (
  SELECT
    hotel,
    roomprice,
    SUM(numberofrooms) AS totalRooms
  FROM
    room
  GROUP BY
    hotel
) ro ON ac.id = ro.hotel
JOIN (
  SELECT
    hotelid,
    SUM(numberofroomsbooked) ASroomsBooked
  FROM
    bookings
  WHERE
    reservationto >= '2016-07-07' AND reservationfrom <= '2016-07-09'
  GROUP BY
    hotelid
) bo ON ac.id = bo.hotelid
WHERE
  ac.active = 1 AND ac.delete = 0
GROUP BY
  ac.id

更新了查询删除了内部选择并将其添加为内部连接

SELECT
  ac.id,
  ac.name,
  ac.link,
  ac.type,
  ac.country,
  co.name AS countryName,
  ac.state,
  st.statename,
  ac.cityid AS cityId,
  ci.name AS cityName,
  ac.addr,
  ac.featuredimage,
  ac.amenities,
  ac.starrating,
  ac.bookings_received,
  ro.roomprice,
  SUM(ro.numberofrooms) AS totalRooms,
  SUM(tm.roomsBooked ) as roomsBooked,
FROM
  accomodations ac
JOIN countries co ON ac.country = co.id
JOIN states st ON ac.state = st.id
JOIN city ci ON ci.id = ac.cityid
JOIN room ro ON ac.id = ro.hotel
(
  SELECT
    hotelid,
    SUM(numberofroomsbooked) AS roomsBooked
  FROM
    bookings
  WHERE
    reservationto >= '2016-07-07' AND reservationfrom <= '2016-07-09'
    group by hotelid
) tm on tm.hotelid=ro.hotel
WHERE
  ac.active = 1 AND ac.delete = 0
GROUP BY
  ac.id