Używanie skorelowanych podzapytań w instrukcji T-SQL
W artykule z zeszłego miesiąca omówiłem, co i jak używać podzapytań w instrukcji T-SQL. W tym miesiącu rozwinę ten temat poprzez omówienie podzapytań skorelowanych. Wyjaśnię, czym jest podzapytanie skorelowane i pokażę kilka różnych przykładów, jak używać podzapytań w instrukcji T-SQL.
Czym jest podzapytanie skorelowane?
Zapytanie skorelowane to instrukcja SELECT zagnieżdżona wewnątrz innej instrukcji T-SQL, która zawiera odniesienie do jednej lub więcej kolumn w zapytaniu zewnętrznym. W związku z tym, można powiedzieć, że podzapytanie skorelowane jest zależne od zapytania zewnętrznego. Jest to główna różnica pomiędzy podzapytaniem skorelowanym a zwykłym podzapytaniem. Zwykłe podzapytanie nie jest zależne od zewnętrznego zapytania, może być uruchomione niezależnie od zewnętrznego zapytania i zwróci zestaw wyników. Podpytanie skorelowane, ponieważ jest zależne od zewnętrznego zapytania, zwróci błąd składni, jeśli zostanie uruchomione samodzielnie.
Podpytanie skorelowane zostanie wykonane wiele razy podczas przetwarzania instrukcji T-SQL, która zawiera podpytanie skorelowane. Podpytanie skorelowane zostanie uruchomione raz dla każdego wiersza kandydującego wybranego przez zapytanie zewnętrzne. Kolumny zapytania zewnętrznego, do których odwołuje się podzapytanie skorelowane, są zastępowane wartościami z wiersza kandydującego przed każdym wykonaniem zapytania. W zależności od wyników wykonania skorelowanego podzapytania, określa ono, czy wiersz zewnętrznego zapytania jest zwracany w końcowym zestawie wyników.
Użycie skorelowanego podzapytania w klauzuli WHERE
Załóżmy, że chcesz uzyskać raport wszystkich „OrderID’s”, gdzie klient nie zakupił więcej niż 10% średniej ilości sprzedanego produktu. W ten sposób można by przejrzeć te zamówienia i ewentualnie skontaktować się z klientami, aby pomóc ustalić, czy był powód zamówienia małej ilości. Skorelowane podzapytanie w klauzuli WHERE może pomóc w stworzeniu tego raportu. Oto instrukcja SELECT, która tworzy żądaną listę „OrderID’s”:
select distinct OrderId from Northwind.dbo. OD where Quantity <l; (select avg(Quantity) * .1 from Northwind.dbo. where OD.ProductID = ProductID)
Podpytanie skorelowane w powyższym poleceniu jest zawarte w nawiasie następującym po znaku większym niż w klauzuli WHERE powyżej. Tutaj możesz zobaczyć, że to skorelowane podzapytanie zawiera odniesienie do „OD.ProductID”. To odniesienie porównuje „ProductID” zewnętrznego zapytania z „ProductID” wewnętrznego zapytania. Podczas wykonywania tego zapytania, silnik SQL wykona zapytanie wewnętrzne, podzapytanie skorelowane, dla każdego rekordu „”. To zapytanie wewnętrzne obliczy średnią „Ilość” dla danego „ProductID” dla wiersza kandydującego przetwarzanego w zapytaniu zewnętrznym. To skorelowane podzapytanie określa, czy zapytanie wewnętrzne zwraca wartość, która spełnia warunek klauzuli WHERE. Jeśli tak, wiersz zidentyfikowany przez zewnętrzne zapytanie jest umieszczany w zbiorze rekordów, który zostanie zwrócony z pełnej instrukcji T-SQL SELECT.
Kod poniżej jest kolejnym przykładem, który wykorzystuje skorelowane podzapytanie w klauzuli WHERE, aby wyświetlić dwóch najlepszych klientów, w oparciu o kwotę dolara związaną z ich zamówieniami, w podziale na regiony. Możesz chcieć wykonać zapytanie w ten sposób, aby nagrodzić tych klientów, ponieważ kupują najwięcej na 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
W tym miejscu możesz zobaczyć, że zapytanie wewnętrzne jest podzapytaniem skorelowanym, ponieważ odwołuje się do „OuterC”, który jest aliasem tabeli „Northwind.DBO.Customer” w zapytaniu zewnętrznym. To wewnętrzne zapytanie używa wartości „Region” do obliczenia dwóch najlepszych klientów dla regionu związanego z wierszem przetwarzanym z zapytania zewnętrznego. Jeśli „CustomerID” z zewnętrznego zapytania jest jednym z dwóch najlepszych klientów, wtedy rekord jest umieszczany w zestawie rekordów do zwrócenia.
Powiązane podzapytanie w klauzuli HAVING
Powiedzmy, że Twoja organizacja chce uruchomić roczny program motywacyjny w celu zwiększenia przychodów. W związku z tym, reklamuje się klientom, że jeśli każde zamówienie, które złożą w ciągu roku, przekroczy 750$, to na koniec roku otrzymasz od nich rabat w wysokości 75$ za każde złożone zamówienie. Poniżej znajduje się przykład, jak obliczyć kwotę rabatu. Ten przykład używa skorelowanego podzapytania w klauzuli HAVING aby zidentyfikować klientów, którzy kwalifikują się do otrzymania rabatu. Oto mój kod dla tego zapytania:
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 )
Przeglądając to zapytanie, można zauważyć, że używam skorelowanego zapytania w klauzuli HAVING do obliczenia całkowitej kwoty zamówienia dla każdego zamówienia klienta. Używam „CustomerID” z zewnętrznego zapytania i roku zamówienia „Datepart(yy,OrderDate)”, aby pomóc zidentyfikować rekordy zamówienia powiązane z każdym klientem, które zostały złożone w roku '1998′. Dla tych powiązanych rekordów obliczam całkowitą kwotę zamówienia, dla każdego zamówienia, sumując wszystkie „” rekordy, używając następującej formuły: sum(UnitPrice * Quantity * (1-Discount)). Jeśli każde zamówienie dla klienta w roku 1998 ma łączną kwotę większą niż 750 dolarów, obliczam kwotę rabatu w zapytaniu zewnętrznym, używając formuły „Count(*)*75”.
Silnik zapytań serweraSQL wykona wewnętrzne skorelowane podzapytanie w klauzuli HAVING tylko dla tych rekordów klienta, które zostały zidentyfikowane w zewnętrznym zapytaniu, lub w zasadzie tylko dla tych klientów, którzy złożyli zamówienia w roku „1998”.
Wykonanie zapytania aktualizującego przy użyciu skorelowanego podzapytania
Skorelowane podzapytanie może być również użyte w zapytaniu aktualizującym. Oto przykład:
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
Oto zestaw wyników, który otrzymam po uruchomieniu tego zapytania na moim komputerze:
A S ----------- ----------- 1 12 23 3
W moim powyższym zapytaniu użyłem podzapytania skorelowanego, aby zaktualizować kolumnę A w tabeli A o sumę kolumny B w tabeli B dla wierszy, które mają taką samą wartość w kolumnie A jak wiersz aktualizowany.
Wniosek
Pozwól mi podsumować. Podkwerenda i skorelowana podkwerenda są zapytaniami SELECT zakodowanymi wewnątrz innego zapytania, zwanego zapytaniem zewnętrznym. Skorelowane podzapytanie i podzapytanie pomagają określić wynik zestawu wyników zwróconego przez pełne zapytanie. Podkwerenda, gdy jest wykonywana niezależnie od zapytania zewnętrznego, zwróci zestaw wyników, a zatem nie jest zależna od zapytania zewnętrznego. Z kolei podzapytanie skorelowane nie może być wykonywane niezależnie od zapytania zewnętrznego, ponieważ używa ono jednego lub więcej odniesień do kolumn w zapytaniu zewnętrznym w celu określenia wyniku zwróconego przez podzapytanie skorelowane. Mam nadzieję, że teraz rozumiesz różnicę między podzapytaniami i podzapytaniami skorelowanymi oraz jak można je wykorzystać w kodzie T-SQL.
” Zobacz wszystkie artykuły autorstwa Gregory’ego A. Larsena