så hvordan får du kombinationer af JobTitle og køn, når tællingen er nul?
Vi kan bruge en cross join. Ideen er først at gøre et kryds deltage på forskellige værdier af køn og titel. Disse resultater kan derefter være ydre sammenføjet tilbage til medarbejdertabellen for at få kontoen.
Så hvad gør denne forespørgsel virkelig?
for at gøre det enklere at skrive og læse brugte jeg CTE ‘ er (almindelige Tabeludtryk) til at oprette denne forespørgsel. Hvis du ikke er bekendt med CTE ‘ er, skal du tænke på dem som synspunkter for nu., Bemærk: Du kan læse mere om CTEs i min artikel Introduktion til almindelige Tabeludtryk.
Vi opretter to CTE ‘ er med forskellige jobtitler og Kønsværdier fra Medarbejdertabellen. Ved at krydse sammenføjning af disse tabeller kan vi vise alle mulige kombinationer af jobtitler og køn.,
Her er den forespørgsel, vi kan bruge til at oprette forskellige kombinationer:
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 ovenstående spørgsmål vil du se KORSET DELTAGE er blot at skabe kombinationer af to forskellige værdsat lister, farve-kodet blå og rød, hvis resultater er:
Nu, alt vi behøver at gøre, er at tage resultaterne og kombinere dem med den opsummerede data, som vi indhenter ved at gruppere data. Igen bruges en CTE til at indsamle de opsummerede data. En ydre sammenføjning bruges derefter til at kombinere dette med alle kombinationer af JobTitle og køn.,
grunden til dette virker er, at vi bruger en ydre JOIN til at returnere alle resultater fra en tabel, uanset om de matcher en anden. Dette er vigtigt, da vi ønsker at medtage alle resultater fra cross join, som er alle kombinationer af køn og titel, uanset om der faktisk er opsummeret data.
for at opsummere er her de trin, vi tager for at oprette dette resultat:
- få en tydelig liste over jobtitler. Dette sker i en CTE.
- få en særskilt liste over køn. Dette sker i en CTE.,
- Opret alle mulige kombinationer af jobtitler og køn ved hjælp af CROSS JOIN.
- Beregn en sammenfattende optælling af medarbejdere efter JobTitle og køn.
- Match den beregnede resum count tæller med en særskilt liste.
Her er den endelige forespørgsel, som udfører disse trin:
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 At gøre det lettere at læse, de to CTE ‘ s og CROSS JOIN til at skabe alle kombinationer af JobTitle og Køn er farvet blå og rød. CTE for at opsummere dataene er farvet grønt.
Bemærk også, at vi bruger s .l COALESCE-funktionen til at erstatte NULL med en nulværdi.,
Her er resultatet af forespørgslen:
INNER JOIN som CROSS JOIN
Som du får at vide, SQL du indse, at der normalt er mere end én måde at skrive en forespørgsel. For eksempel, der er en måde at bruge clausehere-klausulen til at få et kryds til at opføre sig som en indre sammenføjning.
lad os tage disse to tabeller:
Antag, at vi ønsker at forespørge alle ansatte og vise deres fødselsdato og efternavne. For at gøre dette er vi nødt til at relatere Medarbejdertabellen til Person.,
Som vi har set, kan vi skrive et kryds join for at kombinere rækker som så
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
men denne forespørgsel er ikke for nyttig i dette tilfælde, da den returnerer 5,791,880 rækker!
for at begrænse rækkekombinationerne, så personoptegnelserne er korrekt tilpasset medarbejderrækkerne, kan vi bruge en clausehere-klausul. Her er det endelige spørgsmål:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID
Her er de første 14 rækker af 290:
Denne forespørgsel returnerer de samme resultater, som man skrevet med en INNER JOIN., Forespørgslen ved hjælp af en indre sammenføjning er:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
hvilken forespørgsel er bedre? Hvis du ser på forespørgselsplanerne, vil du se, at de er meget ens.
Her er planen for cross join…
Her er planen for inner join…
Som du kan se, at de er identiske. Årsagen til at de er så, er, at s .l er et deklarativt sprog, hvilket betyder, at vi fortæller DB, hvilket resultat vi ønsker, ikke nødvendigvis hvordan man gør det. Når vi leverer DBMS med vores forespørgsel optimi .er sammensætte den bedste plan., I de fleste tilfælde vil det være det samme for tilsvarende udsagn.