时间戳

首页 > 解决方案 > SQL Server 查询以查找没有关联的透视表之间的记录

sql - SQL Server 查询以查找没有关联的透视表之间的记录

问题描述

我正在尝试编写一个查询，该查询返回两个表中的记录列表，其中这些表没有与每个表绑定的记录，或者发现一个标志说不绑定记录。

例如：

房屋

╔════╦══════════════╦
║ Id ║  Name        ║
╠════╬══════════════╬
║  1 ║ 10 Maple     ║
║  2 ║ 20 Oak       ║
║  3 ║ 30 Drive     ║
╚════╩══════════════╩

房屋居住者

╔════╦══════════════╦═════════════╦════════════════╗
║ Id ║  HouseId     ║ OccupentId  ║ LeftOn         ║
╠════╬══════════════╬═════════════╬════════════════╣
║  1 ║ 1            ║  1          ║ NULL           ║
║  2 ║ 1            ║  1          ║ 2019-01-01     ║
║  3 ║ 3            ║  2          ║ 2019-01-01     ║
╚════╩══════════════╩═════════════╩════════════════╝

我正在尝试编写一个 SQL 查询来查找“空”房屋。例如，没有居住者或居住者离开的房屋。最终结果将是：

╔════╦══════════════╦
║ Id ║  Name        ║
╠════╬══════════════╬
║  2 ║ 20 Oak       ║
║  3 ║ 30 Drive     ║
╚════╩══════════════╩

请注意房屋 1 不在此列表中，因为即使居住者离开他又回来了。

到目前为止，我的查询（非常关闭）看起来像：

SELECT *
FROM Houses H
INNER JOIN HousesOccupants HO ON HO.HouseId = H.Id
WHERE NOT EXISTS (
        SELECT *
        FROM HousesOccupants
        WHERE HousesOccupants.LeftOn IS NULL
            AND HO.HouseId = H.Id
        )
    OR (HO.Id IS NULL)

如何修复以下查询以返回正确的数据集？

标签： sqlsql-servertsqlsql-server-2012

解决方案

您的查询有几个问题：

  • 中的子查询not exists与外部查询不相关

  • 即使是，它也会返回居住者离开的房屋，但不会返回从未有居住者的房屋：那些被INNER JOIN. 请注意，ORDed 条件是无操作的，因为再次INNER JOIN消除了从未有居住者的房屋

我只是not exists用来过滤没有记录的房屋houseOccupant，leftOn即null：

select h.*
from houses h
where not exists (
    select 1
    from houseOccupants ho
    where ho.houseId = h.id and ho.leftOn is null
)

推荐阅读