Så hur får du kombinationer av JobTitle och kön när antalet är noll?
vi kan använda en korskoppling. Tanken är att först göra en korskoppling på olika värderingar av kön och titel. Dessa resultat kan sedan yttre sammanfogas tillbaka till personaltabellen för att få kontot.
Så vad gör den här frågan egentligen?
för att göra det enklare att skriva och läsa använde jag CTE: s (vanliga Tabelluttryck) för att skapa den här frågan. Om du inte är bekant med CTE: s, tänk på dem som åsikter för nu., OBS! Du kan läsa mer om CTES i min artikel introduktion till vanliga Tabelluttryck.
Vi skapar två CTE: er av olika JobTitle-och Könsvärden från Personaltabellen. Genom att gå med i dessa tabeller kan vi visa alla möjliga kombinationer av jobbtitlar och kön.,
här är frågan som vi kan använda för att skapa distinkta 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 studerar ovanstående fråga ser du att KORSKODNINGEN bara skapar kombinationer från två separata distinkta värderade listor, färgkodade blå och röd, vars resultat är:
Nu behöver vi bara ta resultaten och kombinera dem med de sammanfattade data vi får genom att gruppera data. Återigen används en CTE för att samla in sammanfattade data. En YTTRE KOPPLING används sedan för att kombinera detta med alla kombinationer av JobTitle och Kön.,
anledningen till att detta fungerar är att vi använder en yttre koppling för att returnera alla resultat från en tabell oavsett om de matchar en annan. Detta är viktigt eftersom vi vill inkludera alla resultat från cross join, vilket är alla kombinationer av kön och titel, oavsett om det faktiskt finns sammanfattade data.
för att sammanfatta, här är de steg vi tar för att skapa detta resultat:
- få en tydlig lista över JobTitles. Detta händer i en CTE.
- få en distinkt lista över könen. Detta händer i en CTE.,
- skapa alla möjliga kombinationer av jobbtitlar och könen med CROSS JOIN.
- Beräkna en sammanfattning av anställda efter JobTitle och kön.
- matcha det beräknade summeringsantalet med en distinkt lista.
här är den slutliga frågan som utför dessa steg:
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;
för att göra det lättare att läsa, de två CTE: s och CROSS JOIN för att skapa alla kombinationer av JobTitle och kön är färgade blå och röd. CTE för att sammanfatta data är färgad grön.
märker också att vi använder SQL COALESCE-funktionen för att ersätta NULL med ett nollvärde.,
här är resultatet av frågan:
INNER JOIN as CROSS JOIN
När du lär känna SQL inser du att det vanligtvis finns mer än ett sätt att skriva en fråga. Det finns till exempel ett sätt att använda WHERE-klausulen för att ha en korskoppling beter sig som en inre koppling.
låt oss ta dessa två tabeller:
Antag att vi vill fråga alla anställda och visa deras födelsedatum och efternamn. För att göra detta måste vi relatera Anställningstabellen till Person.,
som vi har sett kan vi skriva en korskoppling för att kombinera rader som så
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
men den här frågan är inte så användbar i det här fallet, eftersom den returnerar 5,791,880 rader!
för att begränsa radkombinationerna så att personposterna är korrekt matchade med personalraderna kan vi använda en WHERE-klausul. Här är den sista frågan:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID
här är de första 14 raderna av 290:
den här frågan returnerar samma resultat som en skriven med en inre koppling., Frågan med en inre koppling är:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
vilken fråga är bättre? Om du tittar på frågeplanerna ser du att de är mycket lika.
här är planen för cross join…
här är planen för The inner join…
som du ser är de identiska. Anledningen till att de är så är att SQL är ett deklarativt språk, vilket innebär att vi berättar DB vilket resultat vi vill ha, inte nödvändigtvis hur man gör det. När vi ger DBMS med vår fråga optimizer sätta ihop den bästa planen., I de flesta fall kommer det att vara detsamma för motsvarande uttalanden.