Dowiedz się, jak połączyć dane z łączeniem krzyżowym

więc jak uzyskać kombinacje JobTitle i Gender, gdy liczba jest zerowa?

możemy użyć łącznika krzyżowego. Chodzi o to, aby najpierw zrobić krzyż łączący różne wartości płci i tytułu. Wyniki te można następnie połączyć z powrotem do tabeli pracowników, aby uzyskać konto.

co tak naprawdę robi to zapytanie?

aby ułatwić pisanie i czytanie, użyłem CTE (Common Table Expressions) do utworzenia tego zapytania. Jeśli nie jesteś zaznajomiony z CTE, pomyśl o nich jako widoki na teraz., Uwaga: więcej o CTE można przeczytać w moim artykule Wprowadzenie do Common Table Expressions.

tworzymy dwa CTE o różnych wartościach JobTitle i Gender z tabeli pracowników. Poprzez połączenie tych tabel możemy wyświetlić wszystkie możliwe kombinacje stanowisk pracy i płci.,

oto zapytanie, którego możemy użyć do utworzenia odrębnych kombinacji:

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

po przestudiowaniu powyższego zapytania zobaczysz, że połączenie krzyżowe to tylko tworzenie kombinacji z dwóch oddzielnych list wartości, oznaczonych kolorami niebieskim i czerwonym, których wyniki są:

Teraz wszystko, co musimy zrobić, to wziąć wyniki i połączyć je z listą wartości.podsumowane dane otrzymujemy grupując dane. Ponownie CTE jest używany do zbierania podsumowanych danych. Połączenie zewnętrzne jest następnie używane do łączenia tego ze wszystkimi kombinacjami JobTitle i Gender.,

powodem, dla którego to działa, jest to, że używamy zewnętrznego łącza, aby zwrócić wszystkie wyniki z jednej tabeli, niezależnie od tego, czy pasują do innej. Jest to ważne, ponieważ chcemy uwzględnić wszystkie wyniki z cross join, czyli wszystkie kombinacje płci i tytułu, niezależnie od tego, czy istnieją rzeczywiście podsumowane dane.

podsumowując, oto kroki, które podejmujemy, aby stworzyć ten wynik:

1. uzyskaj odrębną listę tytułów Zadań. Dzieje się tak w CTE.
2. uzyskaj odrębną listę płci. Dzieje się tak w CTE.,
3. Utwórz wszystkie możliwe kombinacje tytułów zadań i płci za pomocą połączenia krzyżowego.
4. Oblicz sumaryczną liczbę pracowników według tytułu pracy i płci.
5. Dopasuj obliczoną liczbę podsumowań z odrębną listą.

oto ostateczne zapytanie, które wykonuje te kroki:

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 ułatwić czytanie, dwa CTE i CROSS łączą się, aby utworzyć wszystkie kombinacje JobTitle i Gender są w Kolorze Niebieskim i czerwonym. CTE do podsumowania danych ma kolor zielony.

zauważ również, że używamy funkcji SQL COALESCE, aby zastąpić NULL wartością zerową.,

oto wynik zapytania:

połączenie wewnętrzne jako połączenie krzyżowe

poznając SQL zdajesz sobie sprawę, że zwykle istnieje więcej niż jeden sposób napisania zapytania. Na przykład, istnieje sposób użycia klauzuli WHERE, aby połączenie krzyżowe zachowywało się jak połączenie wewnętrzne.

weźmy te dwie tabele:

Załóżmy, że chcemy sprawdzić wszystkich pracowników i pokazać ich datę urodzenia i nazwisko. Aby to zrobić, musimy powiązać stół pracownika z osobą.,

jak widzieliśmy, możemy napisać połączenie krzyżowe, aby połączyć wiersze w taki sposób

SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P

ale to zapytanie nie jest zbyt przydatne w tym przypadku, ponieważ zwraca 5,791,880 wierszy!

aby ograniczyć kombinacje wierszy, aby rekordy osoby były odpowiednio dopasowane do wierszy pracowników, możemy użyć klauzuli WHERE. Oto ostateczne zapytanie:

SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID

oto pierwsze 14 wierszy 290:

to zapytanie zwraca te same wyniki, co te napisane z łącznikiem wewnętrznym., Zapytanie za pomocą połączenia wewnętrznego to:

SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID

które zapytanie jest lepsze? Jeśli spojrzysz na plany zapytań, zobaczysz, że są one bardzo podobne.

oto plan połączenia krzyżowego…

oto plan połączenia wewnętrznego…

jak widzisz, są identyczne. Powodem, dla którego tak jest, jest to, że SQL jest językiem deklaratywnym, co oznacza, że mówimy DB, jaki wynik chcemy, niekoniecznie jak to zrobić. Kiedy dostarczamy DBMS z naszym zapytaniem optymalizator ułożył najlepszy plan., W większości przypadków będzie to samo dla równoważnych oświadczeń.