Как сравнить несколько записей в нескольких строках для одного и того же идентификатора?
Проблема: я пытаюсь узнать по каждому идентификатору класса, имеет ли сотрудник (empID) хотя бы 1 StateCertArea в FedCertArea, и флаг = 1, если да.
Справочная информация по этому фейковому делу:
- Все classID имеют FederalCertAreas.
- У всех сотрудников есть StateCertAreas.
- Я пытаюсь узнать, есть ли у сотрудника StateCertArea, которая соответствует любой FedCertArea для ClassID.
Мне нужно избегать SELECT в инструкции CASE, если это возможно. Возможно, используя некоторые временные таблицы и соединение, но я воспользуюсь любой помощью.
DROP TABLE IF EXISTS #temptable
CREATE TABLE #temptable ( ClassID int, EmpID decimal(10,0), StateCertArea varchar(4), FedCertArea varchar(4))
INSERT INTO #temptable (ClassID, EmpID, StateCertArea, FedCertArea)
VALUES
( 572888, 77777, '228', '228'),
( 572888, 77777, '389', '389'),
( 572888, 77777, '374', '374'),
( 222555, 77777, '333', '999')
SELECT t.ClassID
, t.EmpID
, t.StateCertArea
, t.FedCertArea
--, CASE WHEN 'EmployeeID has at least 1 StateCertArea in FedCertArea' THEN 1 ELSE 0 END AS IsInArea
FROM #temptable AS t
Ожидаемый результат:
Можете ли вы также указать ожидаемый результат, т. е. завершить минимальный воспроизводимый пример .
выберите случай, когда существует (выберите 1 из некоторой области sa, где sa.areaid = yourtable.areaid), затем 1, иначе 0 закончится как существует,...
Я добавил ожидаемый результат и дополнительный код, чтобы показать, что я хотел бы сделать, если идентификатор сотрудника не был сертифицирован в регионе.
Ответы 2
Символ outer apply обеспечивает эффективный способ запуска коррелированных подзапросов, таких как тот, который необходим здесь, например:
DROP TABLE IF EXISTS #temptable
CREATE TABLE #temptable (ClassID int, EmpID decimal(10,0), StateCertArea varchar(4), FedCertArea varchar(4))
INSERT INTO #temptable (ClassID, EmpID, StateCertArea, FedCertArea)
VALUES
( 572888, 77777, '228', '228'),
( 572888, 77777, '389', '389'),
( 572888, 77777, '374', '374'),
( 222555, 77777, '333', '999')
SELECT DISTINCT
t.ClassID
, t.EmpID
, CASE WHEN oa.HasMatchingArea = 1 THEN 1 ELSE 0 END AS IsInArea
FROM #temptable AS t
OUTER APPLY (
SELECT TOP(1) 1 AS HasMatchingArea
FROM #temptable AS t2
WHERE t2.ClassID = t.ClassID
AND t2.EmpID = t.EmpID
AND t2.StateCertArea = t.FedCertArea
) AS oa
чередуйте использование coalesce
SELECT DISTINCT
t.ClassID
, t.EmpID
, COALESCE(oa.HasMatchingArea,0) AS IsInArea
FROM #temptable AS t
OUTER APPLY (
SELECT TOP(1) 1 AS HasMatchingArea
FROM #temptable AS t2
WHERE t2.ClassID = t.ClassID
AND t2.EmpID = t.EmpID
AND t2.StateCertArea = t.FedCertArea
) AS oa
Это сработало, спасибо!
Мне нужно избегать SELECT в инструкции CASE, если это возможно.
Как насчет CASE в SELECT?
SELECT ClassID, EmpID, MAX(CASE WHEN StateCertArea = FedCertArea THEN 1 ELSE 0 END) IsInArea
FROM #temptable
GROUP BY ClassID, EmpID
Это называется условной агрегацией. Обычно я работаю нормально, и, по крайней мере, работает с образцами данных:
... хотя неясно, что вы хотите, если у вас есть эти строки:
Приведенный выше SQL присвоит столбцу IsInArea значение 0, но возможно, вам понадобится здесь 1.
например, не могли бы вы показать, какого результата вы ожидаете? вы ищете что-то вроде этого: [добавить оператор выбора в другой столбец] , CASE WHEN t2.StateCertificationArea = t3.FederalCertificationArea THEN 1 ELSE 0 END AS testresunt