Så hvordan kan du få kombinasjoner av JobTitle og Kjønn når antallet er null?
Vi kan bruke en cross join. Ideen er å først gjøre et kors delta på forskjellige verdier av kjønn og tittel. Disse resultatene kan da være ytre sluttet seg tilbake til den ansatte-tabellen for å oppnå konto.
Så hva betyr dette søket egentlig gjøre?
for Å gjøre det enklere å skrive og lese, jeg brukte CTE s (Felles Bord Uttrykk) for å lage denne spørringen. Hvis du ikke er kjent med CTE er, tenk på dem som utsikt til nå., Merk: du kan lese mer om CTEs i artikkelen min Introduksjon til Felles Bord Uttrykk.
Vi lage to CTE er av forskjellige JobTitle og Kjønn verdier fra Ansatte-tabellen. Ved å krysse bli med i disse tabellene kan vi vise alle mulige kombinasjoner av Titler og Kjønn.,
Her er den spørringen vi kan bruke til å lage mange kombinasjoner:
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
Når du studerer ovenfor spørringen du vil se på CROSS JOIN er bare å lage kombinasjoner fra to separate tydelig verdsatt lister, fargekodet blått og rødt, hvis resultater er:
Nå, alt vi trenger å gjøre er å ta resultatene og kombinere dem med de summerte data vi oppnå ved å gruppere data. Igjen en CTE er brukt for å samle inn de summerte data. En YTRE SAMMENFØYNING er deretter brukt til å kombinere dette med alle kombinasjoner av JobTitle og Kjønn.,
grunnen til At dette fungerer er at vi bruker en YTRE BLI med å returnere alle resultater fra en tabell, uavhengig av om de samsvarer med et annet. Dette er viktig når vi ønsker å inkludere alle resultater fra korset delta, som er alle kombinasjoner av kjønn og tittel, uavhengig av om det faktisk er oppsummert data.
for Å oppsummere, her er trinnene som vi tar for å lage dette resultatet:
- Få en tydelig liste over JobTitles. Dette skjer i en CTE.
- Få en tydelig liste av Kjønn. Dette skjer i en CTE.,
- til å Lage alle mulige kombinasjoner av Titler og Kjønn ved hjelp av CROSS JOIN.
- Beregne et sammendrag antall ansatte ved JobTitle og Kjønn.
- Matche den beregnede oppsummering regne med en egen liste.
Her er den endelige spørring som oppnår du følgende fremgangsmåte:
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;
for Å gjøre det enklere å lese, de to CTE ‘ s CROSS og BLI med å lage alle kombinasjoner av JobTitle og Kjønn er farget blå og rød. CTE å oppsummere data er farget grønt.
legg Også merke til at vi bruker SQL SAMMENFATTET funksjon for å erstatte NULL med en null-verdi.,
Her er resultatet av spørringen:
INDRE DELTA som CROSS JOIN
Som du blir kjent med SQL-skjønner du at det er vanligvis mer enn én måte å skrive en spørring. For eksempel, det er en måte å bruke den HVOR klausulen har et KORS SLUTTE å oppføre seg som en INNER JOIN.
La oss ta disse to tabeller:
la oss Anta at vi ønsker å spørre alle ansatte og vise sin fødselsdato og etternavn. For å gjøre dette har vi å forholde seg til Ansatte-tabellen til Person.,
Som vi har sett kan vi skrive en cross bli med å kombinere rader som så
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
Men dette spørsmålet er ikke så nyttige i dette tilfellet, så går den tilbake 5,791,880 rader!
for Å begrense rad kombinasjoner slik at personen postene er riktig tilpasset de ansattes rekker vi kan bruke en WHERE-setningsdel. Her er den endelige spørring:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID
Her er de første 14 rader med 290:
Denne spørringen returnerer de samme resultatene som en som er skrevet med en INDRE SAMMENFØYNING., Spørringen ved hjelp av en indre delta er:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
Som spørringen er bedre? Hvis du ser på spørringen planer, vil du se at de er svært like.
Her er planen for cross bli med…
Her er planen for indre bli med…
Som du ser er de identiske. Grunnen til at de er slik, er at SQL er et fortellende språk, betyr at vi fortelle DB hva resultatet vi ønsker, ikke nødvendigvis hvordan du gjør det. Når vi gir DBMS med våre query optimizer satt sammen den beste planen., I de fleste tilfeller vil det være de samme for tilsvarende uttalelser.