sql - 根据条件查找 SQL 表中缺失的条目

Question

我有适度的简单 SQL 经验（在这里使用 MS SQL server 2012），但这避开了我。我希望从表中输出不同的名称（以前从连接成功创建），其中缺少一些必需的条目，但以存在另一个类似条目为条件。对于拥有位置 90 的任何人，我想检查他们也有位置 10 和 20...

例如，考虑这个表：

Name    |Number |Location
--------|-------|--------
Alice   |136218 |90
Alice   |136218 |10
Alice   |136218 |20
Alice   |136218 |40
Bob     |121478 |10
Bob     |121478 |90
Chris   |147835 |20
Chris   |147835 |90
Don     |138396 |20
Don     |138396 |10
Emma    |136412 |10
Emma    |136412 |20
Emma    |136412 |90
Fred    |158647 |90
Gay     |154221 |90
Gay     |154221 |10
Gay     |154221 |30

正式地，我想获得表中那些条目的名称（和编号）：

1. 在位置 90 有一个条目
2. 并且没有所有其他必需的位置条目 - 在这种情况下也是 10 和 20。

所以在上面的例子中

• 此查询不输出 Alice 和 Emma，它们有 90、10 和 20 的条目（全部存在且正确，我们忽略位置 40 条目）。
• 此查询未输出 Don，他没有位置 90 的条目。
• 此查询输出 Bob 和 Gay，它们都缺少位置 20（我们忽略 Gay 的位置 30 条目）。
• 此查询输出 Chris，他缺少位置 10。
• 此查询输出 Fred，他缺少位置 10 和 20。

因此，所需的查询输出类似于：

Name    |Number |Location
--------|-------|--------
Bob     |121478 |20
Chris   |147835 |10
Fred    |158647 |10
Fred    |158647 |20
Gay     |154221 |20

我已经尝试了一些左/右连接的方法，其中 B.Key 为空，并从...中选择，但到目前为止我还不能完全正确地获得逻辑方法。在原始表中，有数十万个条目，只有几十个有效的缺失匹配项。不幸的是，我不能使用任何计算条目的东西，因为查询必须是特定位置的，并且在所需位置之外的其他位置还有其他有效的表条目。

我觉得这样做的正确方法类似于左外连接，但由于起始表是另一个连接的输出，这是否需要声明一个中间表，然后将中间表与其自身外部连接？请注意，无需填写任何空白或将项目输入表中。

任何建议将不胜感激。

===此处粘贴的已回答和使用的代码===

    --STEP 0: Create a CTE of all valid actual data in the ranges that we want
WITH ValidSplits AS
(
    SELECT DISTINCT C.StartNo, S.ChipNo, S.TimingPointId
    FROM Splits AS S INNER JOIN Competitors AS C                    
        ON  S.ChipNo = C.ChipNo                     
            AND (                               
                S.TimingPointId IN (SELECT TimingPointId FROM @TimingPointCheck)
                OR
                S.TimingPointId = @TimingPointMasterCheck
            )
),

--STEP 1: Create a CTE of the actual data that is specific to the precondition of passing @TimingPointMasterCheck
MasterSplits AS
(
    SELECT DISTINCT StartNo, ChipNo, TimingPointId 
    FROM ValidSplits
        WHERE TimingPointId = @TimingPointMasterCheck           
)

--STEP 2: Create table of the other data we wish to see, i.e. a representation of the StartNo, ChipNo and TimingPointId of the finishers at the locations in @TimingPointCheck
--The key part here is the CROSS JOIN which makes a copy of every Start/ChipNo for every TimingPointId
SELECT StartNo, ChipNo, Missing.TimingPointId
FROM MasterSplits
    CROSS JOIN (SELECT * FROM @TimingPointCheck) AS Missing(TimingPointId)
EXCEPT
    SELECT StartNo, ChipNo, TimingPointId FROM ValidSplits
ORDER BY StartNo

标签： sqlsql-serverouter-joinsql-except

Answer 1

欢迎来到堆栈溢出。

您需要的有点挑战性，因为您想查看不存在的数据。因此，我们首先必须创建所有可能的行，然后减去存在的行

    select ppl_with_90.Name,ppl_with_90.Number,search_if_miss.Location 
    from
    (
        select distinct Name,Number
        from yourtable t
        where Location=90
    )ppl_with_90 -- All Name/Numbers that have the 90
    cross join (values (10),(20)) as search_if_miss(Location) -- For all the previous, combine them with both 10 and 20
except -- remove the lines already existing
    select * 
    from yourtable 
    where Location in (10,20)