Comment sélectionner des ID utilisateur distincts avec plusieurs ID de rôle dans SQL ?

Récupération des ID utilisateur répondant à des critères d'ID de rôle spécifiques sur plusieurs lignes

Cet article aborde le défi de la sélection d'ID utilisateur uniques qui satisfont à plusieurs conditions d'ID de rôle réparties sur différentes lignes d'une table de base de données. Considérons un tableau userrole avec des colonnes userid et roleid :

userid	roleid
1	1
1	2
1	3
2	1

L'objectif est d'identifier toutes les valeurs userid distinctes associées aux roleid valeurs 1, 2 et 3. En utilisant les exemples de données, le résultat attendu est userid 1. Deux approches SQL peuvent y parvenir.

Méthode 1 : requête agrégée avec clause HAVING

Cette méthode utilise une fonction d'agrégation et une clause HAVING :

<code class="language-sql">SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(DISTINCT roleid) = 3;</code>

Cette requête filtre d'abord les lignes où roleid vaut 1, 2 ou 3. Ensuite, elle regroupe les résultats par userid et compte le nombre de valeurs distinctes roleid pour chaque utilisateur en utilisant COUNT(DISTINCT roleid). La clause HAVING filtre pour inclure uniquement les utilisateurs avec exactement trois ID de rôle distincts.

Méthode 2 : requête d'auto-jointure

Une alternative plus efficace, en particulier pour les ensembles de données plus volumineux, est une approche d'auto-jointure :

<code class="language-sql">SELECT DISTINCT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t1.userid = t3.userid AND t3.roleid = 3
WHERE t1.roleid = 1;</code>

Cette requête utilise trois auto-jointures : t1 sélectionne les utilisateurs avec roleid 1 ; t2 assure qu'ils en ont également roleid 2 ; et t3 confirme qu'ils ont roleid 3. DISTINCT garantit que seules les valeurs userid uniques sont renvoyées.

L'approche optimale (agrégation ou jointure) dépend de la taille de la table et de la distribution des données. Pour les scénarios avec une faible probabilité de correspondance de lignes, la méthode d'auto-jointure a tendance à mieux fonctionner.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!