php - 选择不在和在

Question

我有这个问题

SELECT COUNT(RoomType) as Available_Rooms 
  FROM Table1 
 WHERE RoomNumber NOT IN (SELECT RoomNumber FROM Table2) 
 GROUP 
    BY RoomType

此查询仅选择可用的房间。我怎样才能修改它以获得不可用的房间。我试着用

SELECT COUNT(RoomType) as Available_Rooms
  FROM Table1 
 WHERE RoomNumber IN (SELECT RoomNumber FROM Table2) 
 GROUP 
    BY RoomType

获取它们，但我如何使用这两个查询让我在 php 的表中回显它们。

Answer 1

在不知道表格的情况下，这仍然未经测试，但您可以按照以下方式尝试:-

select *, count(*) as 'Available_Rooms'
    case
        when `RoomNumber` not in ( select `RoomNumber` from `Table2` ) then
            'Available'
        else
            'Not Available'
    end as 'availability'
from `Table1`
group by `RoomType`