Korrelált alkérdés használata egy T-SQL utasításban
A múlt havi cikkben azt tárgyaltam, hogy mit és hogyan használjunk alkérdést egy T-SQL utasításban. Ebben a hónapban kibővítem ezt a témát a korrelált alkérdések megvitatásával. Elmagyarázom, mi az a korrelált alkérdés, és számos különböző példát mutatok arra, hogyan lehet egy alkérdést használni egy T-SQL utasításban.
Mi az a korrelált alkérdés?
A korrelált alkérdés egy másik T-SQL utasításba ágyazott SELECT utasítás, amely hivatkozást tartalmaz a külső lekérdezés egy vagy több oszlopára. Ezért a korrelált alkérdésről elmondható, hogy függ a külső lekérdezéstől. Ez a fő különbség a korrelált alkérdés és egy egyszerű alkérdés között. Egy egyszerű alkérdés nem függ a külső lekérdezéstől, a külső lekérdezéstől függetlenül futtatható, és eredménykészletet ad vissza. A korrelált alkérdés, mivel függ a külső lekérdezéstől, szintaktikai hibát fog visszaadni, ha önmagában fut.
A korrelált alkérdés többször is végrehajtásra kerül a korrelált alkérdést tartalmazó T-SQL utasítás feldolgozása során. A korrelált alkérdés a külső lekérdezés által kiválasztott minden egyes jelölt sorra egyszer fog lefutni. A korrelált alkérdésben hivatkozott külső lekérdezés oszlopai minden egyes végrehajtás előtt a jelölt sor értékeire cserélődnek. A korrelált alkérdés végrehajtásának eredményétől függően határozza meg, hogy a külső lekérdezés sora visszakerül-e a végső eredményhalmazba.
Korrelált alkérdés használata WHERE záradékban
Tegyük fel, hogy egy jelentést szeretne az összes olyan “OrderID”-ről, ahol az ügyfél nem vásárolta meg egy adott termék átlagos eladott mennyiségének több mint 10%-át. Így felülvizsgálhatná ezeket a megrendeléseket, és esetleg felvehetné a kapcsolatot az ügyfelekkel, hogy segítsen meghatározni, hogy volt-e oka az alacsony mennyiségű megrendelésnek. Egy WHERE záradékban lévő korrelált alkérdés segíthet ennek a jelentésnek az elkészítésében. Íme egy SELECT utasítás, amely a “OrderID-k” kívánt listáját állítja elő:
select distinct OrderId from Northwind.dbo. OD where Quantity <l; (select avg(Quantity) * .1 from Northwind.dbo. where OD.ProductID = ProductID)
A fenti parancsban szereplő korrelált alkérdés a fenti WHERE zárójelben a nagyobb mint jelet követő zárójelben található. Itt látható, hogy ez a korrelált alkérdés tartalmaz egy hivatkozást az “OD.ProductID”-re. Ez a hivatkozás összehasonlítja a külső lekérdezés “ProductID”-jét a belső lekérdezés “ProductID”-jével. Amikor ez a lekérdezés végrehajtásra kerül, az SQL-motor minden egyes “” rekordra végrehajtja a belső lekérdezést, a korrelált alkérdést. Ez a belső lekérdezés kiszámítja az átlagos “Mennyiséget” az adott “TermékID”-re a külső lekérdezésben feldolgozott jelölt sorra vonatkozóan. Ez a korrelált alkérdés határozza meg, hogy a belső lekérdezés olyan értéket ad-e vissza, amely megfelel a WHERE záradék feltételének. Ha igen, akkor a külső lekérdezés által azonosított sor bekerül a teljes T-SQL SELECT utasítás által visszaadott rekordhalmazba.
Az alábbi kód egy másik példa, amely egy korrelált alkérdést használ a WHERE záradékban, hogy megjelenítse az első két ügyfelet a megrendeléseikhez kapcsolódó dollárösszeg alapján régiónként. Érdemes lehet egy ilyen lekérdezést végrehajtani, hogy ezeket a vásárlókat jutalmazni tudja, mivel ők vásárolnak a legtöbbet régiónként.
select CompanyName, ContactName, Address, City, Country, PostalCode from Northwind.dbo.Customers OuterCwhere CustomerID in ( select top 2 InnerC.CustomerId from Northwind.dbo. OD join Northwind.dbo.Orders O on OD.OrderId = O.OrderID join Northwind.dbo.Customers InnerC on O.CustomerID = InnerC.CustomerId Where Region = OuterC.Region group by Region, InnerC.CustomerId order by sum(UnitPrice * Quantity * (1-Discount)) desc ) order by Region
Itt látható, hogy a belső lekérdezés egy korrelált alkérdés, mivel az “OuterC”-re hivatkozik, amely a “Northwind.DBO.Customer” tábla alias táblája a külső lekérdezésben. Ez a belső lekérdezés a “Régió” értéket használja a külső lekérdezésből feldolgozott sorhoz kapcsolódó régió két legjobb ügyfelének kiszámításához. Ha a külső lekérdezés “CustomerID” értéke az első két ügyfél egyike, akkor a rekord bekerül a visszaadandó rekordhalmazba.
Korrelált alkérdés a HAVING záradékban
Tegyük fel, hogy a szervezete egy egész éves ösztönző programot szeretne lefuttatni a bevétel növelése érdekében. Ezért azt hirdetik az ügyfeleiknek, hogy ha az év során leadott minden egyes megrendelésük meghaladja a 750 dollárt, akkor az év végén 75 dolláros visszatérítést biztosítanak számukra megrendelésenként. Az alábbiakban egy példát mutatunk a visszatérítés összegének kiszámítására. Ez a példa egy korrelált alkérdést használ a HAVING záradékban a visszatérítésre jogosult ügyfelek azonosítására. Íme a lekérdezéshez használt kódom:
select C.CustomerID, Count(*)*75 Rebate from Northwind.DBO.Customers C join Northwind.DBO.Orders O on c.CustomerID = O.CustomerID where Datepart(yy,OrderDate) = '1998' group by C.CustomerId having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount)) from Northwind.DBO.Orders O join Northwind.DBO. OD on O.OrderID = OD.OrderID where CustomerID = C.CustomerId and Datepart(yy,OrderDate) = '1998' group by O.OrderId )
A lekérdezést áttekintve láthatja, hogy a HAVING záradékban korrelált lekérdezést használok az egyes vásárlói megrendelések teljes összegének kiszámításához. A külső lekérdezésből származó “CustomerID” és a megrendelés “Datepart(yy,OrderDate)” évét használom, hogy segítsek azonosítani az egyes ügyfelekhez kapcsolódó, ‘1998-ban leadott megrendelési rekordokat. Ezekre a kapcsolódó rekordokra kiszámítom a teljes rendelési összeget, minden egyes rendeléshez, az összes “” rekord összegzésével, a következő képlet segítségével: sum(UnitPrice * Quantity * (1-Discount)). Ha egy ügyfél minden egyes megrendelése az 1998-as évre vonatkozóan 750 dollárnál nagyobb összegű, akkor a külső lekérdezésben a “Szám(*)*75 ” képlet segítségével kiszámítom a kedvezmény összegét.
A SQL Server lekérdezőmotorja csak a külső lekérdezésben azonosított ügyfélrekordokra hajtja végre a belső korrelált alkérdést a HAVING záradékban, vagyis alapvetően csak azokra az ügyfelekre, akik “1998-ban” adtak le rendelést.
Frissítési utasítás végrehajtása korrelált alkérdéssel
A korrelált alkérdés akár egy frissítési utasításban is használható. Íme egy példa:
create table A(A int, S int)create table B(A int, B int)set nocount on insert into A(A) values(1)insert into A(A) values(2)insert into A(A) values(3)insert into B values(1,1)insert into B values(2,1)insert into B values(2,1)insert into B values(3,1)insert into B values(3,1)insert into B values(3,1)update A set S = (select sum(B) from B where A.A = A group by A) select * from Adrop table A,B
Íme az eredményhalmaz, amelyet akkor kapok, ha ezt a lekérdezést futtatom a gépemen:
A S ----------- ----------- 1 12 23 3
A fenti lekérdezésemben a korrelált alkérdést arra használtam, hogy az A táblázat A oszlopát a B táblázat B oszlopának összegével frissítsem azon sorok esetében, amelyek A oszlopában ugyanaz az érték szerepel, mint a frissítendő sorban.
Következtetés
Hadd foglaljam össze. Az alkérdés és a korrelált alkérdés egy másik, külső lekérdezésnek nevezett lekérdezésen belül kódolt SELECT-lekérdezés. A korrelált alkérdés és az alkérdés segít meghatározni a teljes lekérdezés által visszaadott eredményhalmaz kimenetelét. Az alkérdés, ha a külső lekérdezéstől függetlenül hajtják végre, eredményhalmazt ad vissza, és ezért nem függ a külső lekérdezéstől. Ezzel szemben egy korrelált alkérdés nem hajtható végre a külső lekérdezéstől függetlenül, mivel a külső lekérdezés egy vagy több oszlopára való hivatkozást használ a korrelált alkérdés által visszaadott eredményhalmaz meghatározásához. Remélem, hogy most már megértette az allekérdezések és a korrelált allekérdezések különbségét, és azt, hogy hogyan használhatja őket a T-SQL kódjában.
” Gregory A. Larsen oszlopos tag összes cikkének megtekintése