Поиск пропущенных записей в таблице SQL с учетом критериев

У меня скромный опыт работы с 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.

Так в приведенном выше примере

  • Алиса и Эмма не выводятся по этому запросу, у них есть записи для 90, 10 и 20 (все присутствующие и правильные, мы игнорируем позицию 40).
  • Дон не выводится по этому запросу, у него нет записи для местоположения 90.
  • Боб и Гэй выводятся по этому запросу, они оба пропускают местоположение 20 (мы игнорируем запись местоположения Гея 30).
  • Крис выводится по этому запросу, ему не хватает места 10.
  • По этому запросу выводится Фред, ему не хватает мест 10 и 20.

Поэтому желаемый результат запроса выглядит примерно так:

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

Я пробовал несколько подходов с левым / правым объединением, где B.Key имеет значение null и выбирал из... за исключением того, что пока я не могу полностью понять правильный логический подход. В исходной таблице есть сотни тысяч записей и только несколько десятков пропущенных совпадений. К сожалению, я не могу использовать ничего, что подсчитывает записи, так как запрос должен быть привязан к конкретным местоположениям, и есть другие допустимые записи таблицы в других местоположениях за пределами желаемых.

Я чувствую, что правильный способ сделать это - что-то вроде левого внешнего соединения, но поскольку стартовая таблица является результатом другого соединения, требуется ли для этого объявление промежуточной таблицы, а затем внешнее соединение промежуточной таблицы с ее собственным? Обратите внимание, что нет необходимости заполнять какие-либо пробелы или вводить элементы в таблицу.

Любой совет будет очень признателен.

=== Вставленный здесь ответ и использованный код ===

    --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
Источник

2 ответа

Решение

Добро пожаловать в стек переполнения.

То, что вам нужно, немного сложнее, так как вы хотите увидеть данные, которые не существуют. Таким образом, сначала мы должны создать все возможные строки, а затем вычесть те, которые существуют

    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)
Источник

Вам нужно сгенерировать наборы, состоящие из name, number, 10_and_20 для всех строк, где location = 90. Затем вы можете использовать ваш любимый метод (left join + null, not not, not in), чтобы отфильтровать несуществующие строки:

WITH name_number_location AS (
    SELECT t.Name, t.Number, v.Location
    FROM @yourdata AS t
    CROSS JOIN (VALUES (10), (20)) AS v(Location)
    WHERE t.Location = 90
)
SELECT *
FROM name_number_location AS r
WHERE NOT EXISTS (
    SELECT *
    FROM @yourdata AS t
    WHERE r.Name = t.Name AND r.Location = t.Location
)
Источник
Другие вопросы по тегам