Wie wähle ich in SQL eindeutige Benutzer-IDs mit mehreren Rollen-IDs aus?

Abrufen von Benutzer-IDs, die bestimmte Rollen-ID-Kriterien über mehrere Zeilen hinweg erfüllen

In diesem Artikel geht es um die Herausforderung, eindeutige Benutzer-IDs auszuwählen, die mehrere Rollen-ID-Bedingungen erfüllen, die über verschiedene Zeilen innerhalb einer Datenbanktabelle verteilt sind. Betrachten wir eine userrole-Tabelle mit den Spalten userid und roleid:

userid	roleid
1	1
1	2
1	3
2	1

Das Ziel besteht darin, alle eindeutigen userid-Werte zu identifizieren, die mit den roleid-Werten 1, 2 und 3 verknüpft sind. Unter Verwendung der Beispieldaten ist die erwartete Ausgabe userid 1. Zwei SQL-Ansätze können dies erreichen.

Methode 1: Aggregierte Abfrage mit HAVING-Klausel

Diese Methode verwendet eine Aggregatfunktion und eine HAVING-Klausel:

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

Diese Abfrage filtert zunächst Zeilen, in denen roleid 1, 2 oder 3 ist. Dann gruppiert sie die Ergebnisse nach userid und zählt die Anzahl der eindeutigen roleid-Werte für jeden Benutzer, der sie verwendet COUNT(DISTINCT roleid). Die HAVING-Klausel filtert so, dass nur Benutzer mit genau drei unterschiedlichen Rollen-IDs einbezogen werden.

Methode 2: Self-Join-Abfrage

Eine effizientere Alternative, insbesondere für größere Datensätze, ist ein Self-Join-Ansatz:

<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>

Diese Abfrage verwendet drei Selbstverknüpfungen: t1 wählt Benutzer mit roleid 1 aus; t2 stellt sicher, dass sie auch roleid 2 haben; und t3 bestätigt, dass sie roleid 3 haben. DISTINCT stellt sicher, dass nur eindeutige userid Werte zurückgegeben werden.

Der optimale Ansatz (Aggregat vs. Join) hängt von der Tabellengröße und der Datenverteilung ab. Bei Szenarien mit einer geringen Wahrscheinlichkeit übereinstimmender Zeilen ist die Self-Join-Methode tendenziell leistungsstärker.

Das obige ist der detaillierte Inhalt vonWie wähle ich in SQL eindeutige Benutzer-IDs mit mehreren Rollen-IDs aus?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!