Använda en korrelerad underfråga i ett T-SQL-statement
I förra månadens artikel diskuterade jag vad och hur man använder en underfråga i ett T-SQL-statement. Den här månaden kommer jag att utöka det här ämnet genom att diskutera korrelerade underfrågor. Jag kommer att förklara vad en korrelerad underfråga är och visa ett antal olika exempel på hur man kan använda en underfråga i ett T-SQL-uttalande.
Vad är en korrelerad underfråga?
En korrelerad underfråga är ett SELECT-uttalande som är inbäddat i ett annat T-SQL-uttalande och som innehåller en referens till en eller flera kolumner i den yttre frågan. Därför kan den korrelerade underfrågan sägas vara beroende av den yttre frågan. Detta är den största skillnaden mellan en korrelerad underfråga och en vanlig underfråga. En vanlig underfråga är inte beroende av den yttre frågan, kan köras oberoende av den yttre frågan och returnerar en resultatuppsättning. Eftersom en korrelerad underfråga är beroende av den yttre frågan kommer den att ge syntaxfel om den körs själv.
En korrelerad underfråga kommer att exekveras många gånger under behandlingen av T-SQL-anvisningen som innehåller den korrelerade underfrågan. Den korrelerade underfrågan körs en gång för varje kandidatrad som väljs av den yttre frågan. De kolumner i den yttre frågan som refereras i den korrelerade underfrågan ersätts med värden från kandidatraden före varje körning. Beroende på resultatet av utförandet av den korrelerade underfrågan avgörs om raden i den yttre frågan returneras i den slutliga resultatuppsättningen.
Användning av en korrelerad underfråga i en WHERE-klausul
Antag att du vill ha en rapport med alla ”OrderID” där kunden inte köpte mer än 10 % av den genomsnittliga kvantiteten som sålts för en viss produkt. På så sätt kan du granska dessa beställningar och eventuellt kontakta kunderna för att hjälpa till att fastställa om det fanns en orsak till beställningen med låg kvantitet. En korrelerad underfråga i en WHERE-klausul kan hjälpa dig att ta fram den här rapporten. Här är ett SELECT-kommando som producerar den önskade listan över ”OrderID:
select distinct OrderId from Northwind.dbo. OD where Quantity <l; (select avg(Quantity) * .1 from Northwind.dbo. where OD.ProductID = ProductID)
Den korrelerade underfrågan i kommandot ovan finns inom parentesen efter större än-tecknet i WHERE-klausulen ovan. Här kan du se att denna korrelerade underfråga innehåller en referens till ”OD.ProductID”. Denna referens jämför den yttre frågans ”ProductID” med den inre frågans ”ProductID”. När den här frågan utförs kommer SQL-motorn att utföra den inre frågan, den korrelerade underfrågan, för varje ””-post. Denna inre fråga kommer att beräkna den genomsnittliga ”kvantiteten” för det särskilda ”ProductID” för den kandidatrad som behandlas i den yttre frågan. Denna korrelerade underfråga avgör om den inre frågan returnerar ett värde som uppfyller villkoret i WHERE-klausulen. Om så är fallet placeras den rad som identifieras av den yttre frågan i den uppsättning poster som returneras från den fullständiga T-SQL SELECT-anvisningen.
Koden nedan är ett annat exempel där man använder en korrelerad underfråga i WHERE-klausulen för att visa de två största kunderna, baserat på det belopp som deras beställningar uppgår till, per region. Du kanske vill utföra en sådan här fråga så att du kan belöna dessa kunder, eftersom de köper mest per region.
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
Här kan du se att den inre frågan är en korrelerad underfråga eftersom den hänvisar till ”OuterC”, som är tabellaliaset för tabellen ”Northwind.DBO.Customer” i den yttre frågan. Den inre frågan använder värdet ”Region” för att beräkna de två bästa kunderna för den region som är associerad med den rad som bearbetas från den yttre frågan. Om ”CustomerID” i den yttre frågan är en av de två bästa kunderna placeras posten i den postuppsättning som ska returneras.
Korrelerade underfrågor i HAVING-klausulen
Säg att din organisation vill köra ett årslångt incitamentsprogram för att öka intäkterna. Därför annonserar de till era kunder att om varje beställning de gör under året överstiger 750 dollar kommer ni att ge dem en rabatt i slutet av året på 75 dollar per beställning. Nedan följer ett exempel på hur man beräknar rabattbeloppet. I exemplet används en korrelerad underfråga i HAVING-klausulen för att identifiera de kunder som uppfyller kraven för att få rabatten. Här är min kod för den här frågan:
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 )
Om du granskar den här frågan kan du se att jag använder en korrelerad fråga i HAVING-klausulen för att beräkna det totala orderbeloppet för varje kundorder. Jag använder ”CustomerID” från den yttre frågan och beställningens år ”Datepart(yy,OrderDate)” för att identifiera de beställningsposter som är kopplade till varje kund och som placerades 1998. För dessa associerade poster beräknar jag det totala orderbeloppet för varje order genom att summera alla ””-poster med följande formel: sum(UnitPrice * Quantity * (1-Discount)). Om varje enskild beställning för en kund för år 1998 har ett totalt belopp som överstiger 750 dollar, beräknar jag sedan rabattbeloppet i den yttre frågan med hjälp av följande formel: ”Count(*)*75”.
SQL Servers frågemotor kommer endast att utföra den inre korrelerade underfråga i HAVING-klausulen för de kundposter som identifierats i den yttre frågan, eller i princip endast de kunder som gjort beställningar under ”1998”.
Uppförande av ett uppdateringsmeddelande med hjälp av en korrelerad underfråga
En korrelerad underfråga kan även användas i ett uppdateringsmeddelande. Här är ett exempel:
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
Här är den resultatuppsättning jag får när jag kör den här frågan på min maskin:
A S ----------- ----------- 1 12 23 3
I min fråga ovan använde jag den korrelerade underfråga för att uppdatera kolumn A i tabell A med summan av kolumn B i tabell B för de rader som har samma värde i kolumn A som den rad som uppdateras.
Slutsats
Låt mig sammanfatta. En underfråga och en korrelerad underfråga är SELECT-frågor som kodas inuti en annan fråga, som kallas den yttre frågan. Den korrelerade underfrågan och underfrågan hjälper till att bestämma resultatet av den resultatuppsättning som returneras av den fullständiga frågan. När en underfråga utförs oberoende av den yttre frågan returnerar den en resultatuppsättning och är därför inte beroende av den yttre frågan. En korrelerad underfråga kan däremot inte utföras oberoende av den yttre frågan eftersom den använder en eller flera referenser till kolumner i den yttre frågan för att bestämma den resultatmängd som returneras från den korrelerade underfrågan. Jag hoppas att du nu förstår skillnaderna mellan subqueries och correlated subqueries och hur de kan användas i din T-SQL-kod.
” Se alla artiklar av kolumnist Gregory A. Larsen