SQL IN 子句 - 返回不匹配的 IN 元素

Question

>我想运行一个SQL查询来确定许多房间的占用者。我还想看看哪些房间是空的。

我可以使用以下形式的SQL找到居住者：

SELECT Room, OccupantName
FROM Rooms
WHERE Rooms IN ("Room 1", "Room 2", "Room 3", "Room 4")

但是，如果只有房间 1 和 2 有居住者，例如

Room    OccupantName
Room 1, Person A
Room 2, Person B

我怎样才能得到这样的形式：

Room    OccupantName
Room 1, Person A
Room 2, Person B
Room 3, Nobody
Room 4, Nobody

有没有办法选择不返回结果并显示"Nobody"的 IN 子句元素？

Answer 1

您的Rooms表似乎只包含活动占用的数据。 这在很多方面都是有意义的，因为您不想存储有关没有真正占用房间的人的信息。 但是，生成所需的结果集带来了挑战，因为缺少的房间在Rooms中不存在。

这里的一个选项是"日历表"方法。 您可以将包含所有房间的表LEFT JOIN到当前Rooms表中，然后将缺少的占用者标记为nobody。

SELECT t1.Room,
       COALESCE(t2.OccupantName, 'Nobody') AS OccupantName
FROM
(
    SELECT "Room 1" AS Room
    UNION ALL
    SELECT "Room 2"
    UNION ALL
    SELECT "Room 3"
    UNION ALL
    SELECT "Room 4"
) AS t1
LEFT JOIN Rooms AS t2
    ON t1.Room = t2.Rooms

请注意，我使用行子查询为所有房间创建一个表。 实际上，您可以在工作台中创建一个包含此信息的实际表。

在这里演示：

SQLFiddle

Answer 2

假设没有占用者的房间设置为 NULL，您可以使用 case 语句：

SELECT Room, CASE WHEN OccupantName IS NULL THEN 'Nobody' Else OccupantName END
FROM   Rooms
WHERE  Room IN ("Room 1", "Room 2", "Room 3", "Room 4")

或者只是用 where 子句过滤掉它们：

SELECT Room, OccupantName
FROM   Rooms
WHERE  Room IN ("Room 1", "Room 2", "Room 3", "Room 4")
    AND OccupantName IS NOT NULL

Answer 3

您应该有两个单独的表。 一个包含房间列表，另一个在现有表上，然后您可以通过 LEFT 加入经验进行选择。

例如，"房间列表"表将如下所示：

Room
Room 1
Room 2
Room 3
Room 4
Room 5
Room 6
Room 7

然后查询将如下所示：

SELECT Room, OccupantName
FROM RoomsList Left Join Rooms
ON RoomsList.Room=Rooms.Room
WHERE RoomsList.Room IN ("Room 1", "Room 2", "Room 3", "Room 4")

Answer 4

只需一个COALESCE或ISNULL即可帮助您获得所需的输出。您几乎接近您的解决方案，并且在给定的答案中可以看到很多方法，但我建议您稍微更改您的代码，如下所示：

SELECT Room, COALESCE(OccupantName, 'Nobody') AS OccupantName
FROM Rooms
WHERE Rooms IN ('Room 1', 'Room 2', 'Room 3', 'Room 4')

对于MYSQL，您可以以相同的方式使用IFNULL

Answer 5

自从MySQL 8.0引入JSON_TABLE以来，您可以使用下一个方法：

• 从 JSON 房间列表生成表，并对表使用左联接

SELECT 
    all_rooms.room Room, 
    COALESCE(rooms.name, 'NoBody') OccupiedBy
FROM JSON_TABLE(
    '["Room 1","Room 2","Room 3","Room 4"]',
    '$[*]' COLUMNS (room VARCHAR(64) PATH '$[0]')
) all_rooms
LEFT JOIN rooms USING(room);

在线 SQL 编辑器

结果：

+========+============+
| Room   | OccupiedBy |
+========+============+
| Room 1 | Person A   |
+--------+------------+
| Room 2 | NoBody     |
+--------+------------+
| Room 3 | NoBody     |
+--------+------------+
| Room 4 | Person B   |
+--------+------------+