MySQL在查询中使用计数来搜索多行的可用性

Question

我使用一个表mrp来存储多房间物业，并使用第二个表预订来存储物业预订的日期。因此，我有以下表格：

property_idproperty_namenum_rooms

预订(酒店标识，预订标识，日期)

每当预订一个酒店时，都会在bookings表中创建一个条目，因为每个表都有多个房间，所以在同一天可以有多个预订。

我正在使用以下查询：

SELECT * FROM mrp 
   WHERE property_id 
     NOT IN (SELECT property_id FROM booking WHERE `date` >= {$checkin_date} AND `date` <= {$checkout_date}

）

但是，尽管该查询适用于只有一个房间的房产(也就是说，它只列出在您提供的日期之间没有完全预订的房产)，但它不显示已经预订但仍有空房间的房产。我们如何使用count和num_rooms表在我的结果中显示仍然空闲的房间，即使它们在所选日期之间已经有预订，并在我的结果中显示空闲房间的数量。

Answer 1

您必须排除在入住日期和退房日期之间预订的房产。此查询应执行以下操作：

SELECT * FROM srp WHERE property_id NOT IN (
    SELECT property_id FROM booking WHERE `date` >= {$checkin_date} AND `date` <= {$checkout_date}
)

Answer 2

您已接近，但需要更改日期条件并添加一个条件，以匹配外部和内部查询中的记录（均在内部查询的＜code＞WHERE

SELECT * FROM srp 
WHERE NOT EXISTS 
    (SELECT * FROM bookings_srp 
     WHERE srp.booking_id = bookings_srp.booking_id
     AND `date` >= {$check-in_date} AND `date` <= {$check-out_date})

Answer 3

您需要3个级别的查询。最里面的查询将列出在您的日期范围内的任何一天所有房间都已全部预订（或超额预订）的属性和日期。中间的查询将其缩小到一个property_id列表。最外层的查询列出了不在该列表中的所有属性。

SELECT *
FROM mrp
WHERE property_id NOT IN (
    -- List all properties sold-out on any day in range
    SELECT DISTINCT Z.property_id
    FROM (
        -- List sold-out properties by date
        SELECT MM.property_id, MM.num_rooms, BB.adate
        , COUNT(*) as rooms_booked
        FROM mrp MM
        INNER JOIN booking BB on MM.property_id = BB.property_id
        WHERE BB.adate >= @checkin AND BB.adate <= @checkout
        GROUP BY MM.property_id, MM.num_rooms, BB.adate
        HAVING MM.num_rooms - COUNT(*) <= 0
    ) as Z
)