Поиск клиентов, выполняющих все требования

Найти клиентов хотя бы с одной нужной возможностью легко через соединение. Найти тех, у кого есть все требуемые возможности, сложнее: совпадение доказывает наличие, но не полноту набора. Это важно для прав доступа, сертификации, продуктовых комплектов и готовности к миграции.

Все означает отсутствие недостающего

Удобная формулировка: сохранить клиента, если нет отсутствующего требования. Внешний NOT EXISTS ищет невыполненное требование. Внутренний проверяет отсутствие конкретной возможности у этого клиента. Двойное отрицание выражает две точные бизнес-проверки.

CREATE TABLE #Customers(CustomerId int PRIMARY KEY);
CREATE TABLE #Required(Capability varchar(10) PRIMARY KEY);
CREATE TABLE #Has(CustomerId int,Capability varchar(10),
 PRIMARY KEY(CustomerId,Capability));
INSERT #Customers VALUES(1),(2),(3);
INSERT #Required VALUES('A'),('B');
INSERT #Has VALUES(1,'A'),(1,'B'),(1,'C'),(2,'A');
SELECT c.CustomerId FROM #Customers AS c
WHERE NOT EXISTS
(SELECT 1 FROM #Required AS r WHERE NOT EXISTS
 (SELECT 1 FROM #Has AS h WHERE h.CustomerId=c.CustomerId
  AND h.Capability=r.Capability))
ORDER BY c.CustomerId;

Клиент 1 имеет A и B; дополнительное C не мешает. Клиенту 2 недостаёт B, а клиент 3 не имеет возможностей. Первичные ключи гарантируют единственную запись каждого членства и требования.

Запрос недостающих элементов объясняет решение. Вместо одного признака он даёт список исправлений. Команде эксплуатации такой список часто полезнее процента с невидимым знаменателем.

SELECT c.CustomerId,r.Capability AS MissingCapability
FROM #Customers AS c CROSS JOIN #Required AS r
WHERE NOT EXISTS(SELECT 1 FROM #Has AS h
 WHERE h.CustomerId=c.CustomerId AND h.Capability=r.Capability)
ORDER BY c.CustomerId,r.Capability;

Пустые требования и точное совпадение

Если требований нет, первый запрос возвращает всех клиентов: ни у кого ничего не отсутствует. Это обычный логический смысл, однако процесс может запрещать согласование при ненастроенных правилах. Добавьте отдельный EXISTS, проверяющий наличие требований, вместо неявного изменения основной логики.

Все требуемые элементы не означают ровно требуемые элементы. Клиент 1 проходит несмотря на C. Для точного совпадения нужен дополнительный запрет возможностей вне списка требований. Лишний элемент может быть безвредным или запрещённым. Определите это до использования запроса в управлении доступом.

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

Сравнение альтернатив без смены смысла

GROUP BY с COUNT(DISTINCT Capability) тоже применим, если считать только требуемые возможности и сравнивать с правильным числом требований. Подсчёт всех возможностей клиента неверен: любые две возможности не выполняют два конкретных требования. Повторные записи также увеличат обычный COUNT при отсутствии ограничения уникальности.

Ключ клиент-возможность помогает адресным проверкам. Дополнительный индекс возможность-клиент может быть полезен, если поиск начинается с небольшого набора требований. Сопоставьте выигрыш со стоимостью записи и хранения. Исследуйте фактические планы на характерных объёмах: вложенные NOT EXISTS не обязательно превращаются во вложенные процедурные циклы.

Если правила или членство меняются во время оценки, задайте согласованную границу данных для воспроизводимых решений. Сохраняйте версию требований вместе с одобрением, если проверяющим нужно восстановить основание. Запрос текущего состояния не объяснит вчерашнее решение после изменения правил.

Испытайте пустые требования, отсутствие членства, точное совпадение, недостающий элемент, дополнительные элементы и отклоняемые дубли. Проверьте, что список пробелов и итоговое решение используют одну версию правил. Иначе отдельные правильные запросы могут давать противоречивую общую картину. Добавьте клиента с достаточным количеством посторонних возможностей: он не должен проходить только из-за совпадения чисел. Также проверьте обновление требований во время интеграционного теста. Для сохранённых одобрений отдельно задайте срок действия: успешное выполнение старого набора правил не обязано означать соответствие новому набору. Надёжная реализация выбирает правильных клиентов и объясняет пробелы, не смешивая хотя бы один, все и ровно заданные элементы.

При повторной оценке сохраняйте изменившееся требование, чтобы объяснить переход от успешного результата к отказу.

Техническая документация: Microsoft Learn: EXISTS · Microsoft Learn: COUNT.