Trouver les clients répondant à toutes les exigences

Trouver les clients possédant une capacité requise est simple avec une jointure. Trouver ceux qui possèdent toutes les capacités demandées est différent : une correspondance prouve une présence, pas la complétude. Cette nuance intervient dans les habilitations, certifications, offres groupées et validations de migration.

Traduire toutes par aucune manquante

La règle utile est de conserver un client lorsqu'aucune exigence ne manque. Le NOT EXISTS extérieur recherche une exigence non satisfaite. Celui de l'intérieur vérifie l'absence de cette capacité pour le client. Chaque négation correspond donc à une condition métier précise.

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;

Le client 1 satisfait A et B ; C supplémentaire ne le disqualifie pas. Le client 2 manque B. Le client 3 ne possède rien. Les clés primaires imposent une seule occurrence par appartenance et par capacité requise.

Pour expliquer le résultat, exécutez la requête des éléments manquants. Elle transforme une réponse binaire en liste d'actions. Cette liste aide souvent davantage qu'un pourcentage dont on ne voit pas le dénominateur.

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;

Définir le cas vide

Sans exigences, la première requête retourne tous les clients : aucune exigence ne manque à aucun client. C'est le sens logique habituel, mais un processus peut devoir interdire une validation lorsque les règles ne sont pas configurées. Ajoutez alors un EXISTS distinct sur les exigences pour exprimer cette condition.

Toutes les capacités requises ne signifie pas exactement ces capacités. Le client 1 passe malgré C. Pour une correspondance exacte, ajoutez un test rejetant les capacités du client absentes des exigences. Un élément supplémentaire peut être inoffensif ou interdit ; définissez cette règle avant toute décision d'accès.

Évitez des identifiants de capacité NULL sans sémantique définie. Une égalité ne fait pas correspondre deux NULL comme deux codes connus. Les identifiants inconnus doivent être rejetés ou rapprochés en amont, plutôt que considérés comme une satisfaction implicite.

Comparer les formulations correctement

GROUP BY avec COUNT(DISTINCT Capability) peut fonctionner si seules les capacités requises sont comptées et comparées au bon nombre d'exigences. Compter toutes les capacités du client est faux : deux capacités quelconques ne satisfont pas deux capacités précises. Les doublons peuvent également gonfler COUNT sans contrainte d'unicité.

La clé client-capacité facilite les vérifications ciblées. Un index complémentaire capacité-client peut aider lorsque la recherche part d'une petite liste de capacités. Mesurez son bénéfice face aux coûts d'écriture et de stockage. Examinez les plans réels avec des volumes représentatifs ; deux NOT EXISTS ne signifient pas nécessairement deux boucles procédurales.

Lorsque règles et appartenances évoluent, choisissez une vue cohérente pour les décisions reproductibles. Enregistrez la version des exigences avec l'approbation si un réviseur doit comprendre ultérieurement son fondement. Une requête actuelle ne reconstitue pas une décision ancienne après changement des règles.

Testez exigences vides, aucune appartenance, correspondance exacte, capacité manquante, extras et doublons refusés. Vérifiez aussi que la liste des écarts et la décision utilisent la même version des règles. Deux résultats individuellement corrects peuvent sinon se contredire. L'implémentation utile sélectionne et explique sans confondre une capacité, toutes les capacités et exactement les capacités.

Références techniques: Microsoft Learn: EXISTS · Microsoft Learn: COUNT.