Ez az alkérdezésekről szóló cikksorozat második része. Ebben a cikkben az alkérdezéseket a SELECT utasítás oszloplistájában tárgyaljuk. Más cikkek felhasználásukat taglalják más tagmondatokban.
A lecke összes példája a Microsoft SQL Server Management Studio és az AdventureWorks2012 adatbázisra épül. Ezeknek az ingyenes eszközöknek a használatát az Első lépések az SQL Server használatához című útmutatómban találhatja meg.
Alkérdezések használata a Kiválasztási utasításban
Ha egy al lekérdezést az általa megszokott oszloplistában helyeznek el adja vissza az egyes értékeket. Ebben az esetben az allekérdezést egyetlen értékkifejezésnek tekintheti. A visszaadott eredmény nem különbözik a “2 + 2” kifejezéstől Természetesen az alkérdezések visszaadhatják a szöveget is, de érted!
Az alkérdezésekkel való munka során a fő utasítást néha külső lekérdezésnek hívják. Az alkérdezéseket zárójelben foglalják el, így könnyebben észrevehetőek .
Legyen körültekintő az alkérdezések használatakor. Lehet, hogy szórakoztatóak a használatuk, de amint többet ad hozzá a lekérdezéshez, lassítani tudják a lekérdezést.
Egyszerű lekérdezés az átlag kiszámításához
Kezdjük egy egyszerű lekérdezéssel a SalesOrderDetail megjelenítéséhez, és hasonlítsuk össze az általános átlagos SalesOrderDetail LineTotal értékkel. A SELECT utasítás, amelyet használni fogunk:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Ez a lekérdezés a következőképpen jelenít meg eredményeket:
A futtatáskor a fenti pirossal végzett lekérdezés először fut le hogy megkapja az átlagos LineTotal értéket.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Ez az eredmény ezután visszadugódik az oszloplistába, és a lekérdezés folytatódik. Számos dologra szeretnék rámutatni. :
- Az al lekérdezések zárójelben vannak feltüntetve .
- Amikor az alkérdezéseket egy SELECT utasításban használják, akkor csak egy értéket adhatnak vissza. Ennek logikusnak kell lennie, ha egyszerűen egy oszlop kiválasztásával egy értéket ad vissza egy sorhoz, és ugyanazt a mintát kell követnünk.
- Általánosságban elmondható, hogy az allekérdezést csak egyszer futtatják a teljes lekérdezéshez, és eredményét újra felhasználják. . Ez azért van, mert a lekérdezés eredménye nem változik az egyes visszaküldött soroknál.
- Fontos, hogy az oszlopnevekhez álneveket kell használni az olvashatóság javítása érdekében.
Egyszerű lekérdezés a kifejezésben
Amint az várható, hogy az allekérdezés eredménye felhasználható más kifejezésekben. Az előző példára építve használjuk az allekérdezést annak meghatározására, hogy a LineTotalunk mennyiben tér el az átlagtól.
A szórás egyszerűen a LineTotal mínusz az átlagos Line teljes összeggel. A következő allekérdezésben kékre színeztem. Itt van a variancia képlete:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
A zárójelbe zárt SELECT utasítás az allekérdezés. A korábbi példához hasonlóan ez a lekérdezés is egyszer futtatni fog, visszaad egy numerikus értéket, amelyet ezután kivonnak az egyes LineTotal értékekből.
Itt van a végső formátumú lekérdezés:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Íme az eredmény:
Amikor kiválasztott utasításokban dolgozom alkérdezésekkel, akkor általában felépítem és először tesztelje az allekérdezést. A SELECT utasítások nagyon gyorsan bonyolódhatnak. A legjobb, ha apránként építjük fel őket. A különféle darabok külön elkészítésével és tesztelésével valóban segítséget nyújt a hibakeresésben.
Összefüggő lekérdezések
A külső lekérdezés értékeit be lehet építeni az allekérdezés záradékaiba. Ezeket a típusú lekérdezéseket korrelált részlekérdezéseknek nevezzük, mivel az al lekérdezés eredményei valamilyen formában kapcsolódnak a külső lekérdezés értékeihez. A korrelált lekérdezéseket néha szinkronizált lekérdezéseknek is hívják.
Ha nem tudja tudni, mi az összefüggés, olvassa el ezt a definíciót a Google-tól:
Correlate: “kölcsönös kapcsolat vagy kapcsolat van, amelyben az egyik dolog befolyásolja vagy függ a másiktól. ”
A korrelált részlekérdezés tipikus felhasználása a külső lekérdezés egyik oszlopát használja a belső lekérdezés WHERE záradékában. Ez sok esetben józan ész, korlátozza a belső lekérdezést az adatok egy részhalmazára.
Összehasonlított részlekérdezési példa
Korrelált allekérdezési példát adunk azáltal, hogy jelentést adunk az egyes SalesOrderDetail LineTotal és az átlagos LineTotal értékekről a teljes értékesítésre vonatkozóan. Rendelés.
Ez a kérés jelentősen eltér a korábbi példáinktól, mivel az általunk kiszámított átlag eltér az egyes megrendeléseknél.
Itt lépnek kapcsolatba az egymással összefüggő alkérdezések. Használhatunk egy értéket a külső lekérdezésből, és illessze be az allekérdezés szűrőfeltételeibe.
Vegyünk egy a-t nézd meg, hogyan számoljuk ki az átlagos sorösszeget. Ehhez összeállítottam egy illusztrációt, amely a SELECT utasítást mutatja meg allekérdezéssel.
A diagram. A SELECT utasítás két részből áll, a külső lekérdezésből és az allekérdezésből. A külső lekérdezés az összes SalesOrderDetail sor lekérésére szolgál.Az allekérdezés egy adott SalesOrderID értékesítési rendelés részleteinek sorainak megkeresésére és összefoglalására szolgál.
Ha a lépéseket verbalizálnám fogjuk venni, összefoglalnám őket a következőképpen:
- Szerezd meg a SalesOrderID-t.
- Visszaadjuk az átlagos LineTotal-t az összes SalesOrderDetail elemből, ahol a SalesOrderID megegyezik.
- Haladjon tovább a külső SalesOrderID-re a külső lekérdezésben, és ismételje meg az 1. és 2. lépést.
Az AdventureWork2012 adatbázisban futtatható lekérdezés:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Itt vannak a lekérdezés eredményei:
Van néhány elemeket, amelyekre felhívni a figyelmet.
- Láthatja, hogy oszlopaliasokat használtam a lekérdezés eredményeinek könnyebb olvashatóságának elősegítésére.
- Táblázat-álnevet (SOD) is használtam a külső lekérdezés. Ez lehetővé teszi a külső lekérdezés értékeinek felhasználását az allekérdezésben. Ellenkező esetben a lekérdezés nincs összefüggésben!
- A táblák álneveinek használatával egyértelművé válik, hogy az egyes táblázatok mely oszlopokat tartalmazzák.
A korrelált részlekérdezés lebontása
Próbáljuk meg ezt lebontani az SQL használatával.
Kezdésként tegyük fel, hogy csak megkapjuk a SalesOrderDetailID 20 példáját. A megfelelő SalesOrderID 43661.
Könnyű megszerezni az elem átlagos LineTotal-ját.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Ez visszaadja a 2181.765240 értéket.
Most, hogy megvan az átlag, csatlakoztassa a lekérdezésünkhöz
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Alkérdezések használatával ez
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
A végső lekérdezés :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Korrelált allekérdezés másik táblával
A korrelált allekérdezés, vagy ami azt illeti, bármelyik lekérdezés, más táblázatot használhat a külső lekérdezést. Ez hasznos lehet, ha egy “szülő” táblával dolgozik, például a SalesOrderHeader, és az eredménybe szeretne foglalni egy összefoglalót a gyermek sorokból, például a SalesOrderDetail-ből.
Visszaadjuk a OrderDate, TotalDue és az értékesítési rendelések részleteinek sorai. Ehhez a következő diagram segítségével nyerhetjük el a véleményünket:
Ehhez a SELECT utasításunkba bekerül egy összefüggő alkérdezés, amely visszaadja a SalesOrderDetail sorok számát. A külső lekérdezés SalesOrderID-jének szűrésével biztosítjuk, hogy a helyes SalesOrderDetail tételt számoljuk.
Itt van a végső SELECT utasítás:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Az eredmények a következők:
Ebben a példában észre kell venni a következőket:
- Az allekérdezés a külső lekérdezéstől eltérő táblából választja ki az adatokat.
- A táblázatot és oszlop álnevek az SQL és az eredmények könnyebb olvasása érdekében.
- Ne felejtse el kétszer ellenőrizni k a hol záradék! Ha elfelejtette feltüntetni a tábla nevét vagy álneveit az allekérdezés WHERE záradékban, akkor a lekérdezés nem lesz összefüggésben.
Összefüggő allekérdezések a belső csatlakozásokkal
Fontos annak megértése, hogy ugyanazokat az eredményeket elérheti akár egy allekérdezéssel, akár a csatlakozással. Bár mindkettő ugyanazt az eredményt adja, az egyes módszereknek vannak előnyei és hátrányai!
Tekintsük az utolsó példát, ahol a SalesHeader tételek sorait számoljuk.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Ugyanezt a lekérdezést egy INNER JOIN használatával, a GROUP BY-vel együtt lehet megtenni
SELECT SO.SalesOrderID, OrderDate, TotalDue, COUNT(SOD.SalesOrderDetailID) as LineCountFROM Sales.SalesOrderHeader SO INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SO.SalesOrderIDGROUP BY SO.SalesOrderID, OrderDate, TotalDue
Melyik gyorsabb?
Rájön, hogy sokan azt mondják, hogy kerüljék az alkérdezéseket, mivel lassabbak. Azt fogják állítani, hogy a korrelált részlekérdezésnek egyszer végre kell hajtania a külső lekérdezésben visszaadott minden sort, míg a Belső Csatlakozásnak csak egy adatot kell áttennie az adatokon.
Magam? Mondom, nézd meg a lekérdezési tervet. A fenti példákhoz a saját tanácsomat követtem, és a terveket azonosnak találtam!
Ez nem azt jelenti, hogy a tervek megváltoznának, ha több adat lenne, de a véleményem az, hogy nem csak feltételezéseket kell tennie. A legtöbb SQL DBMS-optimalizáló nagyon jól tudja kitalálni a lekérdezés végrehajtásának legjobb módját. Fogják venni a szintaxisaikat, például egy allekérdezést vagy a INNER JOIN, és felhasználni őket egy a tényleges végrehajtási terv.
Melyiket könnyebb elolvasni?
Attól függően, hogy mi érzi jól magát, a INNER JOIN példa könnyebben olvasható, mint a kapcsolódó lekérdezés. ebben a példában szeretem a korrelált allekérdezést, mivel közvetlenebbnek tűnik. Könnyebb számomra látni, hogy mit számítanak.
A Belső Csatlakozás véleményem szerint kevésbé közvetlen. Először meg kell látnia, hogy az összes értékesítési adatsort kézben adják vissza, majd összesítik. Ezt csak akkor kapja meg, ha elolvassa a teljes állítást.
Melyik a jobb?
Mondja el, mit gondol. Szeretném megtudni, hogy a korrelált allekérdezést vagy az INNER JOIN példát szeretné-e használni.