Фильтровать строки SQL с неуникальными идентификаторами по другому значению
У меня есть довольно большой SQL-запрос, который выдает следующий оператор (упрощенный для ясности):
Однако мне нужно отфильтровать этот результат и получить только те LabId, у которых NO CapacityId = 4. В этом примере ожидаемый результат — только LabId 7.
Вот рабочий пример, воспроизводящая ситуацию: http://sqlfiddle.com/#!18/e581a/2
С этим запросом:
CREATE TABLE Lab (LabId int, CapacityId int)
INSERT INTO Lab (LabId, CapacityId)
VALUES (1, 1), (1, 2), (1, 3), (1, 4), (2, 1),(2, 2)
SELECT LabId
FROM Lab
WHERE CapacityId != 4
GROUP BY LabId
Он по-прежнему извлекает обе лаборатории, потому что в обеих есть по крайней мере одна строка, где CapacityId != 4
Как мне WHERE/GROUP BY получать только лаборатории, у которых NO CapacityId = 4?
Ответы 3
Вы можете использовать having:
SELECT LabId
FROM Lab
GROUP BY LabId
HAVING SUM(CASE WHEN CapacityId = 4 THEN 1 ELSE 0 END) = 0;
SUM() суммирует количество строк для каждого LabId, в котором 4 обозначает емкость. = 0 указывает, что их нет.
Примечание. Это не возвращает LabId, у которых нет строк в этой таблице (которые также технически соответствуют этому условию). Для этого используйте NOT EXISTS. Позвольте мне предположить, что таблицы названы немного по-другому:
SELECT l.LabId
FROM Labs l
WHERE NOT EXISTS (SELECT 1
FROM LabCapacity lc
WHERE lc.LabId = l.LabId AND
lc.CapacityId = 4
);
Вы можете использовать существующую логику здесь:
SELECT l1.LabId
FROM Lab l1
WHERE NOT EXISTS (SELECT 1 FROM Lab l2 WHERE l2.LabId = l1.LabId AND
l2.CapacityId = 4);
Вы также можете использовать агрегацию:
SELECT LabId
FROM Lab
GROUP BY LabId
HAVING COUNT(CASE WHEN CapacityId = 4 THEN 1 END) = 0;
Использовать аналитическую функцию
SELECT LabId
FROM (select labId, CapacityId,
sum(case when CapacityId=4 then 1 else 0 end) over(partition by labId) cnt_capacyty4
from Lab
)
WHERE cnt_capacyty4 = 0
GROUP BY LabId