SQL IN子句-取回不匹配的IN元素 [英] SQL IN Clause - get back IN elements that did not match

问题描述

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

I'd like to run an SQL query to determine the occupants of a number of rooms. I'd also like to see which rooms are empty.

我可以使用以下形式的SQL查找居住者:

I can find the occupants using SQL of the form:

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

但是，如果只有1号和2号会议室有人，例如

However, if only Rooms 1 and 2 have occupants, eg

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

我如何获得某种形式的东西:

how can I get something of the form:

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

是否可以选择没有返回结果并显示"Nobody"的IN子句的元素?

Is there a way to select out the elements of the IN clause that did not return results and show "Nobody"?

推荐答案

看来，您的Rooms表仅包含活动占用数据.从许多方面讲，这都是有道理的，因为您不想存储有关实际上并没有占用房间的人的信息.但是，要生成所需的结果集会带来挑战，因为Rooms中不存在缺少的房间.

It appears that your Rooms table only contains data for active occupancies. This makes sense in many ways, because you don't want to store information about people which aren't really occupying a room. But it poses a challenge to generate your desired result set, because the missing rooms aren't present in Rooms.

这里的一个选项是日历表"方法.您可以LEFT JOIN包含当前Rooms桌子的所有房间的桌子，然后将缺少的居住者标记为nobody.

One option here is the "calendar table" approach. You can LEFT JOIN a table containing all rooms to your current Rooms table, and then label missing occupants as 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

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

Note that I used an line subquery to create a table for all rooms. In practice, you could create an actual table in Workbench containing this information.

此处演示:

SQLFiddle

这篇关于SQL IN子句-取回不匹配的IN元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！