Verwendung einer korrelierten Subquery in einer T-SQL-Anweisung

Juni 2, 2021admin

Im Artikel des letzten Monats habe ich erörtert, was und wie man eine Subquery in einer T-SQL-Anweisung verwendet. Diesen Monat werde ich dieses Thema erweitern, indem ich auf korrelierte Unterabfragen eingehe. Ich erkläre, was eine korrelierte Subquery ist, und zeige verschiedene Beispiele für die Verwendung einer Subquery in einer T-SQL-Anweisung.

Was ist eine korrelierte Subquery?

Eine korrelierte Subquery ist eine SELECT-Anweisung, die in eine andere T-SQL-Anweisung eingebettet ist und einen Verweis auf eine oder mehrere Spalten in der äußeren Abfrage enthält. Daher kann man sagen, dass die korrelierte Unterabfrage von der äußeren Abfrage abhängig ist. Dies ist der Hauptunterschied zwischen einer Correlated Subquery und einer einfachen Subquery. Eine einfache Unterabfrage ist nicht von der äußeren Abfrage abhängig, kann unabhängig von der äußeren Abfrage ausgeführt werden und gibt eine Ergebnismenge zurück. Eine korrelierte Subquery, die von der äußeren Abfrage abhängig ist, gibt einen Syntaxfehler zurück, wenn sie allein ausgeführt wird.

Eine korrelierte Subquery wird während der Verarbeitung der T-SQL-Anweisung, die die korrelierte Subquery enthält, mehrmals ausgeführt. Die korrelierte Unterabfrage wird einmal für jede von der äußeren Abfrage ausgewählte Kandidatenzeile ausgeführt. Die Spalten der äußeren Abfrage, auf die in der Correlated Subquery verwiesen wird, werden vor jeder Ausführung durch Werte aus der Kandidatenzeile ersetzt. Je nach den Ergebnissen der Ausführung der korrelierten Unterabfrage wird bestimmt, ob die Zeile der äußeren Abfrage in der endgültigen Ergebnismenge zurückgegeben wird.

Verwendung einer korrelierten Unterabfrage in einer WHERE-Klausel

Angenommen, Sie möchten einen Bericht über alle „OrderIDs“ erstellen, bei denen der Kunde nicht mehr als 10 % der durchschnittlichen Verkaufsmenge eines bestimmten Produkts gekauft hat. Auf diese Weise könnten Sie diese Bestellungen überprüfen und sich möglicherweise mit den Kunden in Verbindung setzen, um festzustellen, ob es einen Grund für die geringe Bestellmenge gibt. Mit einer korrelierten Unterabfrage in einer WHERE-Klausel können Sie diesen Bericht erstellen. Hier ist eine SELECT-Anweisung, die die gewünschte Liste von „OrderID’s“ erzeugt:

select distinct OrderId from Northwind.dbo. OD where Quantity <l; (select avg(Quantity) * .1 from Northwind.dbo. where OD.ProductID = ProductID)

Die korrelierte Unterabfrage im obigen Befehl ist in der Klammer enthalten, die dem Größer-als-Zeichen in der obigen WHERE-Klausel folgt. Hier können Sie sehen, dass diese korrelierte Unterabfrage einen Verweis auf „OD.ProductID“ enthält. Dieser Verweis vergleicht die „ProductID“ der äußeren Abfrage mit der „ProductID“ der inneren Abfrage. Wenn diese Abfrage ausgeführt wird, führt die SQL-Engine die innere Abfrage, die korrelierte Unterabfrage, für jeden „“-Datensatz aus. Diese innere Abfrage berechnet die durchschnittliche „Menge“ für die bestimmte „ProductID“ für die Kandidatenzeile, die in der äußeren Abfrage verarbeitet wird. Diese korrelierte Unterabfrage bestimmt, ob die innere Abfrage einen Wert zurückgibt, der die Bedingung der WHERE-Klausel erfüllt. Wenn dies der Fall ist, wird die von der äußeren Abfrage identifizierte Zeile in die Datensatzmenge aufgenommen, die von der vollständigen T-SQL SELECT-Anweisung zurückgegeben wird.

Der folgende Code ist ein weiteres Beispiel, das eine korrelierte Unterabfrage in der WHERE-Klausel verwendet, um die beiden wichtigsten Kunden auf der Grundlage des mit ihren Bestellungen verbundenen Dollarbetrags pro Region anzuzeigen. Vielleicht möchten Sie eine Abfrage wie diese durchführen, um diese Kunden zu belohnen, da sie pro Region am meisten kaufen.

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

Hier sehen Sie, dass die innere Abfrage eine korrelierte Unterabfrage ist, da sie auf „OuterC“ verweist, die der Tabellenalias für die Tabelle „Northwind.DBO.Customer“ in der äußeren Abfrage ist. Diese innere Abfrage verwendet den Wert „Region“, um die beiden wichtigsten Kunden für die Region zu berechnen, die mit der verarbeiteten Zeile der äußeren Abfrage verbunden ist. Wenn die „CustomerID“ der äußeren Abfrage einer der beiden Top-Kunden ist, wird der Datensatz in die zurückgegebene Datensatzmenge eingefügt.

Korrelierte Unterabfrage in der HAVING-Klausel

Angenommen, Ihr Unternehmen möchte ein ganzjähriges Incentive-Programm zur Umsatzsteigerung durchführen. Sie werben daher bei Ihren Kunden dafür, dass Sie ihnen am Ende des Jahres einen Rabatt in Höhe von 75 Dollar pro Bestellung gewähren, wenn sie im Laufe des Jahres mehr als 750 Dollar bestellen. Im Folgenden finden Sie ein Beispiel für die Berechnung des Rabattbetrags. In diesem Beispiel wird eine korrelierte Unterabfrage in der HAVING-Klausel verwendet, um die Kunden zu identifizieren, die für den Rabatt in Frage kommen. Hier ist mein Code für diese Abfrage:

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 )

Bei Betrachtung dieser Abfrage können Sie sehen, dass ich eine korrelierte Abfrage in der HAVING-Klausel verwende, um den Gesamtbetrag für jede Kundenbestellung zu berechnen. Ich verwende die „CustomerID“ aus der äußeren Abfrage und das Jahr der Bestellung „Datepart(yy,OrderDate)“, um die mit jedem Kunden verbundenen Bestellungsdatensätze zu identifizieren, die im Jahr ‚1998‘ aufgegeben wurden. Für diese zugehörigen Datensätze berechne ich den Gesamtbetrag der Bestellung für jede Bestellung, indem ich alle „“-Datensätze mit der folgenden Formel zusammenzähle: sum(UnitPrice * Quantity * (1-Discount)). Wenn jede einzelne Bestellung für einen Kunden für das Jahr 1998 einen Gesamtdollarbetrag von mehr als 750 hat, berechne ich dann den Rabattbetrag in der äußeren Abfrage mit dieser Formel „Count(*)*75“.

Die Abfrage-Engine von SQL Server führt die innere korrelierte Unterabfrage in der HAVING-Klausel nur für die in der äußeren Abfrage identifizierten Kundendatensätze aus, d. h. im Grunde nur für die Kunden, die im Jahr „1998“ Bestellungen aufgegeben haben.

Durchführen einer Aktualisierungsanweisung mit einer korrelierten Unterabfrage

Eine korrelierte Unterabfrage kann sogar in einer Aktualisierungsanweisung verwendet werden. Hier ein Beispiel:

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

Hier ist die Ergebnismenge, die ich erhalte, wenn ich diese Abfrage auf meinem Rechner ausführe:

A S ----------- ----------- 1 12 23 3

In meiner obigen Abfrage habe ich die korrelierte Unterabfrage verwendet, um Spalte A in Tabelle A mit der Summe von Spalte B in Tabelle B für Zeilen zu aktualisieren, die denselben Wert in Spalte A haben wie die zu aktualisierende Zeile.

Schlussfolgerung

Zusammenfassend kann man sagen. Eine Subquery und eine korrelierte Subquery sind SELECT-Abfragen, die innerhalb einer anderen Abfrage, der so genannten äußeren Abfrage, kodiert sind. Die korrelierte Unterabfrage und die Unterabfrage tragen dazu bei, das Ergebnis der von der vollständigen Abfrage zurückgegebenen Ergebnismenge zu bestimmen. Eine Unterabfrage, die unabhängig von der äußeren Abfrage ausgeführt wird, gibt eine Ergebnismenge zurück und ist daher nicht von der äußeren Abfrage abhängig. Eine korrelierte Subquery kann dagegen nicht unabhängig von der äußeren Abfrage ausgeführt werden, da sie einen oder mehrere Verweise auf Spalten in der äußeren Abfrage verwendet, um die Ergebnismenge zu bestimmen, die von der korrelierten Subquery zurückgegeben wird. Ich hoffe, dass Sie nun den Unterschied zwischen Unterabfragen und korrelierten Unterabfragen verstehen und wie sie in Ihrem T-SQL-Code verwendet werden können.

“ Alle Artikel von Kolumnist Gregory A. Larsen ansehen