Aflați cum să combinați datele cu o îmbinare încrucișată

Deci, cum obțineți combinații de JobTitle și sex atunci când numărul este zero?

putem folosi o cruce se alăture. Ideea este de a face mai întâi o cruce se alăture pe valori distincte de gen și titlu. Aceste rezultate pot fi apoi exterior alăturat înapoi la tabelul angajat pentru a obține contul.

deci, ce face această interogare într-adevăr?

pentru a simplifica scrierea și citirea, am folosit CTE (Common Table Expressions) pentru a crea această interogare. Dacă nu sunteți familiarizați cu CTE, gândiți-vă la ele ca opinii deocamdată., Notă: puteți citi mai multe despre CTEs în articolul meu Introducere în expresii de masă comune.

creăm două CTE-uri cu valori distincte JobTitle și Gender din tabelul angajaților. Prin alăturarea încrucișată a acestor tabele putem afișa toate combinațiile posibile de titluri de locuri de muncă și de gen.,

Aici este de interogare putem folosi pentru a crea combinații distincte:

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

după ce veți studia interogarea de mai sus veți vedea CROSS JOIN este doar crearea de combinații de două separate, distincte de prim rang liste, culori albastru și roșu, ale căror rezultate sunt:

Acum, tot ce trebuie să faceți este să ia rezultatele și să le combine cu rezumate de date se obține prin gruparea datelor. Din nou, un CTE este utilizat pentru a colecta datele rezumate. Un JOIN exterior este apoi utilizat pentru a combina acest lucru cu toate combinațiile de JobTitle și Gen.,

motivul pentru care funcționează este că folosim un JOIN exterior pentru a returna toate rezultatele de la o masă, indiferent dacă se potrivesc cu alta. Acest lucru este important, deoarece dorim să includem toate rezultatele de la Cross join, care este toate combinațiile de gen și titlu, indiferent dacă există de fapt date rezumate.pentru a rezuma, iată pașii pe care îi facem pentru a crea acest rezultat:

1. obțineți o listă distinctă de JobTitles. Acest lucru se întâmplă într-un CTE.
2. obțineți o listă distinctă de sexe. Acest lucru se întâmplă într-un CTE.,
3. creați toate combinațiile posibile de titluri de locuri de muncă și sexe folosind CROSS JOIN.
4. calculați un număr sumar de angajați în funcție de JobTitle și sex.
5. potriviți numărul de rezumat calculat cu o listă distinctă.

Aici este ultima interogare care realizează acești pași:

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;

Pentru a face mai ușor de citit, cele două CTE și CRUCEA se ALĂTURE pentru a crea toate combinațiile de Ibm și de Gen sunt colorate în albastru și roșu. CTE pentru a rezuma datele este colorat verde.

de asemenea, observați că folosim funcția SQL COALESCE pentru a înlocui NULL cu o valoare zero.,

iată rezultatul interogării:

inner JOIN as cross JOIN

pe măsură ce cunoașteți SQL, vă dați seama că există de obicei mai multe modalități de a scrie o interogare. De exemplu, există o modalitate de a folosi clauza WHERE pentru a avea o îmbinare încrucișată să se comporte ca o îmbinare interioară.

Să luăm aceste două tabele:

să Presupunem că dorim să interogare tuturor angajaților și data nașterii și numele de familie. Pentru a face acest lucru, trebuie să raportăm tabelul angajaților la persoană.,

după Cum am văzut, putem scrie o cruce se alăture pentru a combina rânduri ca asa

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

Dar această interogare nu este prea util în acest caz, după cum se întoarce 5,791,880 rânduri!

pentru a limita combinațiile de rânduri, astfel încât înregistrările persoanelor să fie potrivite în mod corespunzător cu rândurile angajaților, putem folosi o clauză WHERE. Aici este ultima întrebare:

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

Aici sunt primele 14 rânduri de 290:

Această interogare returnează aceleași rezultate ca unul scris cu un INNER JOIN., Interogarea folosind o îmbinare interioară este:

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

care interogare este mai bună? Dacă te uiți la planurile de interogare, vei vedea că sunt foarte asemănătoare.

Aici este planul de cruce se alăture…

Aici este planul pentru inner join…

după Cum vedeți, ele sunt identice. Motivul pentru care sunt așa, este că SQL este un limbaj declarativ, ceea ce înseamnă că spunem DB ce rezultat Ne dorim, nu neapărat cum să o facem. Când furnizăm SGBD cu interogarea noastră, Optimizatorul a pus la punct cel mai bun plan., În cele mai multe cazuri, va fi același pentru declarațiile echivalente.