Tak jak se vám kombinace JobTitle a Pohlaví, kdy počet je nula?
můžeme použít křížový spoj. Cílem je nejprve udělat křížové spojení na odlišných hodnotách pohlaví a titulu. Tyto výsledky pak mohou být vnější připojen zpět do tabulky zaměstnanců získat účet.
Co tedy tento dotaz skutečně dělá?
aby bylo psaní a čtení jednodušší, použil jsem k vytvoření tohoto dotazu výrazy CTE (Common Table Expressions). Pokud nejste obeznámeni s CTE je, myslet na ně jako názory pro tuto chvíli., Poznámka: více o CTEs si můžete přečíst v mém článku Úvod do běžných výrazů tabulky.
z tabulky zaměstnanců vytvoříme dvě CTE s odlišným pracovním názvem a genderovými hodnotami. Křížovým spojením těchto tabulek můžeme zobrazit všechny možné kombinace pracovních titulů a pohlaví.,
Tady je dotaz můžeme použít k vytvoření silnější kombinace:
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
Jakmile budete studovat výše uvedený dotaz uvidíte CROSS PŘIPOJIT, je to jen vytváření kombinací z dva oddělené odlišné oceňují seznamy, barevně modrá a červená, jejichž výsledky jsou:
vše, co musíme udělat, je vzít výsledky a kombinovat je s sumarizované údaje získáme sloučením údajů. Opět CTE se používá ke shromažďování souhrnných dat. Vnější spojení se pak používá ke kombinaci se všemi kombinacemi JobTitle a pohlaví.,
důvodem, proč to funguje, je to, že používáme vnější spojení, abychom vrátili všechny výsledky z jedné tabulky bez ohledu na to, zda se shodují s jinou. To je důležité, protože chceme zahrnout všechny výsledky z kříže připojit, což je kombinace pohlaví a titul, bez ohledu na to, zda je vlastně souhrnné údaje.
shrnout, zde jsou kroky, které podnikáme k vytvoření tohoto výsledku:
- získat zřetelný seznam JobTitles. To se děje v CTE.
- získejte zřetelný seznam pohlaví. To se děje v CTE.,
- vytvořte všechny možné kombinace pracovních titulů a pohlaví pomocí křížového spojení.
- vypočítat souhrnný počet zaměstnanců podle názvu zaměstnání a pohlaví.
- Porovnejte vypočtený souhrnný počet s odlišným seznamem.
Tady je poslední dotaz, který provede tyto kroky:
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;
Aby bylo snazší číst, dva CTE a KŘÍŽOVÉ spojení vytvořit všechny kombinace JobTitle a Pohlaví jsou barevné modré a červené. CTE pro shrnutí dat je zbarveno zeleně.
také si všimneme, že používáme funkci SQL COALESCE k nahrazení NULL nulovou hodnotou.,
zde je výsledek dotazu:
vnitřní spojení jako CROSS JOIN
Jak poznáte SQL, uvědomujete si, že je obvykle více než jeden způsob, jak napsat dotaz. Existuje například způsob, jak pomocí klauzule WHERE mít křížové spojení chovat se jako vnitřní spojení.
Vezměme si tyto dvě tabulky:
Předpokládejme, že chceme dotazovat všechny zaměstnance a ukázat jejich datum narození a příjmení. K tomu musíme spojit tabulku zaměstnanců s osobou.,
Jak jsme viděli, můžeme napsat cross join spojit řádky jako tak,
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
Ale tento dotaz není příliš užitečné v tomto případě, jako se to vrátí 5,791,880 řádky!
Chcete-li omezit kombinace řádků, takže záznamy osob jsou správně přizpůsobeny řadám zaměstnanců, můžeme použít klauzuli WHERE. Tady je poslední dotaz:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID
Zde jsou prvních 14 řádků 290:
Tento dotaz vrátí stejné výsledky jako jeden napsané s VNITŘNÍ spojení., Dotaz pomocí vnitřního spojení je:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
který dotaz je lepší? Pokud se podíváte na plány dotazu, uvidíte, že jsou velmi podobné.
Zde je plán pro cross připojit…
Zde je plán pro vnitřní spojení…
Jak můžete vidět, že jsou totožné. Důvodem, proč tomu tak je, je to, že SQL je deklarativní jazyk, což znamená, že řekneme DB, jaký výsledek chceme, ne nutně, jak to udělat. Když poskytneme DBMS s naším dotazem, optimalizátor sestaví nejlepší plán., Ve většině případů to bude stejné pro ekvivalentní prohlášení.