php - 在 SQL 语句中，如果没有找到特定查询的任何内容，则返回一个条目

Question

这似乎有点令人费解。但是，如果您可以快速阅读这篇文章中的答案: Combining two SQL queries PDO

这是我正在使用的示例 sqlfiddle: http://sqlfiddle.com/#!2/c4983/3

所以这里应该发生的是在我的 sql 查询中:

SELECT r.RoomID AS RoomID,
       RoomName,
       NumOfRooms,
       MaxPeopleExistingBeds,
       MaxExtraBeds,
       MaxExtraPeople,
       CostPerExtraPerson,
       MaximumFreeChildren,
       IncludeBreakfast,
       MinRate
FROM Rooms r
JOIN RoomDetails rd
    ON r.RoomID = rd.RoomID
JOIN (
    SELECT b.RoomID,
           AccommodationID,
           count(b.RoomID) AS Bookings
    FROM Booking b
    WHERE ArriveDate >= :aDate
      AND DepartDate <= :dDate
    GROUP BY RoomID
) t
    ON t.AccommodationID = r.AccommodationID
WHERE r.AccommodationID = :aid
    AND t.Bookings < NumOfRooms
GROUP BY RoomName

目前它会检查每个房间的预订是否满员。然后显示它们。但是，如果容量为 1，则没有预订。它不会显示房间。

是否可以通过重构此语句来解​​决此问题？

Answer 1

首先，您加入了 AccommodationID 并且您忽略了 Booking 表的房间 ID，这给您错误的房间预订计数，您也需要加入带有房间 ID 并且您还需要使用 LEFT 加入，所以如果预订没有返回任何信息，那么这意味着您的房间可用，当使用 ON( t.AccommodationID =r.AccommodationID AND r .RoomID = t.RoomID) 您的预订列将为空(即没有对该房间进行预订)，因此您需要额外的 where 条件来检查预订是否为空然后返回此房间，因为它可用于有客人

SELECT r.RoomID AS RoomID,
       RoomName,
       NumOfRooms,
       t.Bookings,
       MaxPeopleExistingBeds,
       MaxExtraBeds,
       MaxExtraPeople,
       CostPerExtraPerson,
       MaximumFreeChildren,
       IncludeBreakfast,
       MinRate
FROM Rooms r
LEFT JOIN RoomDetails rd
    ON r.RoomID = rd.RoomID
LEFT JOIN (
    SELECT b.RoomID,
           AccommodationID,
          COALESCE(count(b.RoomID),0) AS Bookings
    FROM Booking b
    WHERE ArriveDate >= '2014-02-26'
      AND DepartDate <= '2014-03-10'
    GROUP BY RoomID
) t
    ON( t.AccommodationID = r.AccommodationID 
       AND r.RoomID = t.RoomID)
WHERE r.AccommodationID = 1
   AND (t.Bookings < NumOfRooms OR t.Bookings IS NULL)
GROUP BY RoomName;

Fiddle Demo