tehát hogyan kap kombinációk JobTitle és a nem, ha a szám nulla?
tudjuk használni a kereszt csatlakozni. Az ötlet az, hogy először csinál egy kereszt csatlakozzon a különböző értékek A nemek és a cím. Ezek az eredmények ezután külső csatlakozott vissza a munkavállalói asztalra, így a számla.
tehát mit csinál ez a lekérdezés?
ahhoz, hogy egyszerűbb legyen írni és olvasni, a CTE (Common Table Expressions) kifejezést használtam a lekérdezés létrehozásához. Ha nem ismeri a CTE-ket, gondoljon rájuk, mint nézetekre., Megjegyzés: a CTE-kről többet olvashat a cikkemben Bevezetés a közös Táblázatkifejezésekbe.
a dolgozói táblázatból két CTE-t különböztetünk meg a különböző Munkakörökből és a nemek értékeiből. A táblázatokhoz való csatlakozással megjeleníthetjük a Munkacímek és a nemek összes lehetséges kombinációját.,
itt van a lekérdezés tudjuk használni, hogy hozzon létre különböző kombinációk:
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
miután tanulmányozta a fenti lekérdezés látni fogja a kereszt csatlakozni csak létre kombinációk két különálló értékes listákat, színkódolt kék és piros, amelynek eredményei:
most, csak annyit kell tennünk, hogy az eredményeket, és össze őket az összesített adatokat az adatok csoportosításával kapjuk meg. Ismét egy CTE-t használnak az összesített adatok gyűjtésére. Ezután egy külső csatlakozással kombinálják ezt a JobTitle és a nem minden kombinációjával.,
ennek oka az, hogy külső csatlakozást használunk az összes eredmény visszaküldéséhez az egyik táblából, függetlenül attól, hogy megegyeznek-e a másikkal. Ez azért fontos, mert szeretnénk, hogy tartalmazza az összes eredményt a cross join, amely mind a kombinációk A nemek és a cím, függetlenül attól, hogy van-e ténylegesen össze adatokat.
összefoglalni, itt vannak a lépéseket teszünk, hogy hozzon létre ezt az eredményt:
- kap egy külön listát Álláscímek. Ez egy CTE-ben történik.
- kap egy külön listát a nemek. Ez egy CTE-ben történik.,
- hozzon létre minden lehetséges kombinációját Álláscímek és nemek segítségével CROSS JOIN.
- Számolja ki az alkalmazottak összefoglaló számát Álláscímek és nemek szerint.
- illessze össze a számított összefoglaló számot egy külön listával.
itt van az utolsó lekérdezés, amely elvégzi ezeket a lépéseket:
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;
Az olvasás megkönnyítése érdekében a két CTE és a CROSS csatlakozik a JobTitle és a nem minden kombinációjának létrehozásához kék és piros színű. Az adatok összefoglalására szolgáló CTE zöld színű.
azt is észre használjuk az SQL COALESCE függvény helyett NULL nulla értéket.,
itt van a lekérdezés eredménye:
belső csatlakozás KERESZTCSATLAKOZÁSKÉNT
mivel megismered az SQL-t, rájössz, hogy általában több módja van egy lekérdezés írására. Például, van egy módja annak, hogy a hol záradékot, hogy egy kereszt csatlakozni viselkednek, mint egy belső csatlakozni.
vegyük ezt a két táblázatot:
tegyük fel, hogy minden alkalmazottat meg akarunk kérdezni, és meg akarjuk mutatni születési dátumukat és vezetéknevüket. Ehhez a munkavállalói táblát személyhez kell kapcsolnunk.,
ahogy láttuk, írhatunk egy keresztcsatlakozást a sorok kombinálásához, így
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
de ez a lekérdezés ebben az esetben nem túl hasznos, mivel 5,791,880 sort ad vissza!
a sorkombinációk korlátozásához, hogy a személyrekordok megfelelően illeszkedjenek a munkavállalói sorokhoz, használhatunk egy WHERE záradékot. Itt van a végső lekérdezés:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID
itt vannak a 290 első 14 sorai:
Ez a lekérdezés ugyanazokat az eredményeket adja vissza, mint egy belső csatlakozással írt., A belső csatlakozást használó lekérdezés:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
melyik lekérdezés jobb? Ha megnézed a lekérdezési terveket, látni fogod, hogy nagyon hasonlóak.
itt van a keresztcsatlakozás terve…
itt van a belső csatlakozás terve …
mint látja, azonosak. Ennek oka az, hogy az SQL deklaratív nyelv, ami azt jelenti, hogy elmondjuk a DB-nek, hogy milyen eredményt akarunk, nem feltétlenül, hogyan kell csinálni. Amikor a DBMS-t lekérdezésünkkel biztosítjuk, az optimalizáló összeállította a legjobb tervet., A legtöbb esetben ugyanaz lesz az egyenértékű állítások esetében.