Wie erhalten Sie Kombinationen aus JobTitle und Gender, wenn die Anzahl Null ist?
Wir können einen Cross Join verwenden. Die Idee ist, zuerst einen Cross Join für unterschiedliche Werte von Geschlecht und Titel durchzuführen. Diese Ergebnisse können dann automatisch wieder mit der Employee-Tabelle verknüpft werden, um das Konto zu erhalten.
Was macht diese Abfrage wirklich?
Um das Schreiben und Lesen zu vereinfachen, habe ich CTE (Common Table Expressions) verwendet, um diese Abfrage zu erstellen. Wenn Sie mit CTE ‚ s nicht vertraut sind, betrachten Sie sie vorerst als Ansichten., Hinweis: Sie können mehr über CTEs in meinem Artikel Einführung in allgemeine Tabellenausdrücke lesen.
Aus der Employee-Tabelle erstellen wir zwei CTE mit unterschiedlichen JobTitle-und Gender-Werten. Durch die Verknüpfung dieser Tabellen können wir alle möglichen Kombinationen von Berufsbezeichnungen und Geschlecht anzeigen.,
Hier ist die Abfrage, die wir verwenden können, um verschiedene Kombinationen zu erstellen:
WITH cteJobTitle (JobTitle)AS (SELECT DISTINCT JobTitle FROM HumanResources.Employee),cteGender (Gender)AS (SELECT DISTINCT Gender FROM HumanResources.Employee)SELECT J.JobTitle, G.GenderFROM cteJobTitle AS J CROSS JOIN cteGender AS GORDER BY J.JobTitle
Sobald Sie die obige Abfrage studiert haben, sehen Sie, dass der CROSS JOIN nur Kombinationen aus zwei separaten, unterschiedlich bewerteten Listen erstellt, farbcodiert blau und rot, deren Ergebnisse sind:
Jetzt müssen wir nur noch die Ergebnisse nehmen und sie mit den zusammengefassten Daten kombinieren, die wir durch Gruppieren der Daten erhalten. Wieder wird ein CTE verwendet, um die zusammengefassten Daten zu sammeln. Ein ÄUßERER JOIN wird dann verwendet, um dies mit allen Kombinationen von JobTitle und Gender zu kombinieren.,
Der Grund, warum dies funktioniert, ist, dass wir einen ÄUßEREN JOIN verwenden, um alle Ergebnisse aus einer Tabelle zurückzugeben, unabhängig davon, ob sie mit einer anderen übereinstimmen. Dies ist wichtig, da wir alle Ergebnisse aus dem Cross Join einbeziehen möchten, bei dem es sich um alle Kombinationen von Geschlecht und Titel handelt, unabhängig davon, ob tatsächlich Daten zusammengefasst sind.
Zusammenfassend sind hier die Schritte aufgeführt, die wir ausführen, um dieses Ergebnis zu erstellen:
- Holen Sie sich eine eindeutige Liste von JobTitles. Dies geschieht in einem CTE.
- Holen Sie sich eine eindeutige Liste der Geschlechter. Dies geschieht in einem CTE.,
- Erstellen Sie alle möglichen Kombinationen von Berufsbezeichnungen und Geschlechtern mit CROSS JOIN.
- Berechnen Sie eine zusammenfassende Anzahl der Mitarbeiter nach JobTitle und Geschlecht.
- Passen Sie die berechnete Zusammenfassungszahl mit einer eindeutigen Liste an.
Hier ist die letzte Abfrage, die diese Schritte ausführt:
WITH cteJobTitle (JobTitle)AS (SELECT DISTINCT JobTitle FROM HumanResources.Employee), cteGender (Gender)AS (SELECT DISTINCT Gender FROM HumanResources.Employee), cteCounts (JobTitle, Gender, NumberEmployees)AS (SELECT JobTitle, Gender, COUNT(1) AS NumberEmployees FROM HumanResources.Employee GROUP BY JobTitle, Gender)SELECT J.JobTitle, G.Gender, COALESCE (C.NumberEmployees, 0) as NumberEmployeesFROM cteJobTitle AS J CROSS JOIN cteGender AS G LEFT OUTER JOIN cteCounts AS C ON C.JobTitle = J.JobTitle AND C.Gender = G.GenderORDER BY J.JobTitle, G.Gender;
Um das Lesen zu erleichtern, sind die beiden CTE ‚ s und CROSS JOIN zum Erstellen aller Kombinationen von JobTitle und Gender blau und rot gefärbt. Die CTE zum Zusammenfassen der Daten ist grün gefärbt.
Beachten Sie auch, dass wir die SQL-COALESCE-Funktion verwenden, um NULL durch einen Nullwert zu ersetzen.,
Hier ist das Ergebnis der Abfrage:
INNER JOIN as CROSS JOIN
Wenn Sie SQL kennenlernen, stellen Sie fest, dass es normalerweise mehr als eine Möglichkeit gibt, eine Abfrage zu schreiben. Zum Beispiel gibt es eine Möglichkeit, die WHERE Klausel zu verwenden, damit sich ein CROSS JOIN wie ein INNER JOIN verhält.
Nehmen wir diese beiden Tabellen:
Angenommen, wir möchten alle Mitarbeiter abfragen und ihr Geburtsdatum und ihren Nachnamen anzeigen. Dazu müssen wir die Mitarbeitertabelle mit der Person in Beziehung setzen.,
Wie wir gesehen haben, können wir einen Cross Join schreiben, um Zeilen wie folgt zu kombinieren
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
Aber diese Abfrage ist in diesem Fall nicht allzu nützlich, da sie 5,791,880 Zeilen zurückgibt!
Um die Zeilenkombinationen zu begrenzen, damit die Personendatensätze ordnungsgemäß mit den Mitarbeiterzeilen übereinstimmen, können wir eine WHERE Klausel verwenden. Hier ist die letzte Abfrage:
Hier sind die ersten 14 Zeilen von 290:
Diese Abfrage gibt die gleichen Ergebnisse wie eine mit einem INNEREN JOIN geschriebene zurück., Die Abfrage mit einem inneren Join lautet:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
Welche Abfrage ist besser? Wenn Sie sich die Abfragepläne ansehen, werden Sie feststellen, dass sie sehr ähnlich sind.
Hier ist der plan für den cross join…
Hier ist der plan für den inner join…
Wie Sie sehen, sind Sie identisch. Der Grund, warum sie so sind, ist, dass SQL eine deklarative Sprache ist, dh wir sagen der DB, welches Ergebnis wir wollen, egal wie es geht. Wenn wir dem DBMS unsere Abfrage zur Verfügung stellen, hat der Optimierer den besten Plan zusammengestellt., In den meisten Fällen ist es für äquivalente Anweisungen gleich.