SQL - поиск основных записей, которые НЕ имеют общего значения с подробными записями

#sql #sql-server #tsql

Вопрос:

Возможно, этот вопрос был задан, но я не уверен, как сформулировать это в резюме, поэтому, возможно, я его пропустил.

Моя ситуация:

У меня есть основная запись (упрощенная):

    ServiceOrder
     ID         - String (15)
     Status     - String (1)

У каждого основного есть подробные записи

 SODetail
 ID           - String (15)
 LineNbr      - int 
 Status       - String (CP)

SODetail и Master объединяются ПО идентификатору столбца

Статус закрыт в главном, когда есть значение «C»

Статус закрыт в SODetail при наличии значения «CP»

Мне нужна команда SQL, которая найдет все основные записи, значение которых НЕ равно «C», но где все его подробные записи имеют статус «CP»

Другими словами, что-то произошло, когда для некоторых заказов на обслуживание не было установлено значение «закрыто», когда для всех его подробных записей было установлено значение «закрыто». Мне нужно их найти, чтобы мы могли их исправить.

Я знаю, что есть команда, которая может дать мне это, но почему-то она ускользает от меня.

Ответ №1:

Хитрость здесь в том, чтобы работать в обратном направлении.

Первая задача состоит в том, чтобы определить, какие идентификаторы имеют статус CP

 SELECT  ID
FROM    SODetail
GROUP BY ID
HAVING  COUNT(*) = SUM(CASE WHEN Status = 'CP' THEN 1 ELSE 0 END)

Выше приведен список идентификаторов, в которых в качестве статуса указан только «CP». Он делает это путем подсчета количества подробных записей и подсчета количества подробных записей с ‘CP’ в качестве их статуса и возврата идентификаторов, в которых эти показатели совпадают.

Имея соответствующие идентификаторы, указанные выше, вы можете легко просто присоединить их к своей основной записи и отфильтровать по статусу основной записи.

 SELECT  SO.ID
FROM    ServiceOrder AS SO
        INNER JOIN 
            (SELECT   ID
              FROM    SODetail
              GROUP BY ID
              HAVING  COUNT(*) = SUM(CASE WHEN Status = 'CP' THEN 1 ELSE 0 END)
            ) AS SOD ON SO.ID = SOD.ID
WHERE   SO.Status <> 'C'

Редактировать: дополнительные пояснения и некоторые пояснения