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

Question

我正在使用一张表 mrp 来存储多房间属性，并使用第二个表 booking 来存储属性被预订的日期。因此，我有以下表格:

mrp(property_id, property_name, num_rooms)

booking(property_id, booking_id, date)

每当预订一处特性时，都会在预订表中输入一个条目，因为每个表都有多个房间，所以它可以在同一天进行多个预订。

我正在使用以下查询:

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

)

但是，尽管此查询对于只有一个房间的酒店(也就是说，它只列出在您提供的日期之间还没有完全被预订的酒店)来说工作得很好，但它不会显示已经预订但仍然有的酒店空房间。我们如何使用计数和 num_rooms 表在我的结果中显示仍然空置的房间，即使他们已经在所选日期之间进行了预订，并在我的结果中显示空闲房间的数量。

Answer 1

您需要 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
)