Použití korelovaného poddotazu v příkazu T-SQL

V minulém článku jsem se zabýval tím, co a jak používat poddotaz v příkazu T-SQL. Tento měsíc toto téma rozšířím a proberu korelované poddotazy. Vysvětlím, co je to korelovaný poddotaz, a ukážu několik různých příkladů, jak poddotaz v příkazu T-SQL použít.

Co je to korelovaný poddotaz?

Korelovaný poddotaz je příkaz SELECT vnořený do jiného příkazu T-SQL, který obsahuje odkaz na jeden nebo více sloupců ve vnějším dotazu. Lze tedy říci, že korelovaný poddotaz je závislý na vnějším dotazu. To je hlavní rozdíl mezi korelovaným poddotazem a obyčejným poddotazem. Obyčejný poddotaz není závislý na vnějším dotazu, lze jej spustit nezávisle na vnějším dotazu a vrátí sadu výsledků. Korelovaný poddotaz, protože je závislý na vnějším dotazu, vrátí syntaktické chyby, pokud je spuštěn samostatně.

Korelovaný poddotaz bude proveden mnohokrát při zpracování příkazu T-SQL, který obsahuje korelovaný poddotaz. Korelovaný poddotaz bude proveden jednou pro každý kandidátský řádek vybraný vnějším dotazem. Sloupce vnějšího dotazu, na které odkazuje korelovaný poddotaz, jsou před každým provedením nahrazeny hodnotami z kandidátního řádku. V závislosti na výsledcích provedení korelovaného poddotazu se určí, zda bude řádek vnějšího dotazu vrácen v konečné sadě výsledků.

Použití korelovaného poddotazu v klauzuli WHERE

Předpokládejme, že chcete sestavu všech „OrderID“, u nichž zákazník nenakoupil více než 10 % průměrného prodaného množství daného produktu. Tímto způsobem byste mohli tyto objednávky zkontrolovat a případně kontaktovat zákazníky, abyste pomohli zjistit, zda existuje důvod pro objednávku malého množství. Tuto sestavu vám pomůže vytvořit korelovaný poddotaz v klauzuli WHERE. Zde je příkaz SELECT, který vytvoří požadovaný seznam „OrderID“:

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

Korelativní poddotaz ve výše uvedeném příkazu je obsažen v závorce za znaménkem větší než v klauzuli WHERE výše. Zde můžete vidět, že tento korelovaný poddotaz obsahuje odkaz na „OD.ProductID“. Tento odkaz porovnává „ProductID“ vnějšího dotazu s „ProductID“ vnitřního dotazu. Při provádění tohoto dotazu provede SQL engine vnitřní dotaz, korelovaný poddotaz, pro každý záznam „“. Tento vnitřní dotaz vypočítá průměrné „Množství“ pro konkrétní „ProductID“ pro kandidátský řádek zpracovávaný ve vnějším dotazu. Tento korelovaný poddotaz určí, zda vnitřní dotaz vrátí hodnotu, která splňuje podmínku klauzule WHERE. Pokud ano, je řádek identifikovaný vnějším dotazem zařazen do množiny záznamů, která bude vrácena z kompletního příkazu T-SQL SELECT.

Níže uvedený kód je dalším příkladem, který používá korelovaný poddotaz v klauzuli WHERE k zobrazení dvou nejlepších zákazníků podle výše částky v dolarech spojené s jejich objednávkami v jednotlivých regionech. Možná budete chtít provést takový dotaz, abyste mohli tyto zákazníky odměnit, protože nakupují nejvíce 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

Zde vidíte, že vnitřní dotaz je korelovaný poddotaz, protože odkazuje na „OuterC“, což je alias tabulky „Northwind.DBO.Customer“ ve vnějším dotazu. Tento vnitřní dotaz používá hodnotu „Region“ k výpočtu dvou nejlepších zákazníků pro region spojený se zpracovávaným řádkem z vnějšího dotazu. Pokud je „CustomerID“ vnějšího dotazu jedním ze dvou nejlepších zákazníků, pak je záznam zařazen do množiny záznamů, která má být vrácena.

Související poddotaz v klauzuli HAVING

Řekněme, že vaše organizace chce spustit celoroční motivační program pro zvýšení tržeb. Proto svým zákazníkům oznámí, že pokud každá jejich objednávka, kterou během roku učiní, přesáhne 750 USD, poskytnete jim na konci roku slevu ve výši 75 USD za každou učiněnou objednávku. Níže je uveden příklad výpočtu výše rabatu. Tento příklad používá korelovaný poddotaz v klauzuli HAVING k identifikaci zákazníků, kteří mají nárok na slevu. Zde je můj kód pro tento dotaz:

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 ) 

Při prohlížení tohoto dotazu můžete vidět, že používám korelovaný dotaz v klauzuli HAVING pro výpočet celkové částky objednávky pro každou objednávku zákazníka. Používám „CustomerID“ z vnějšího dotazu a rok objednávky „Datepart(yy,OrderDate)“, abych pomohl identifikovat záznamy objednávky spojené s každým zákazníkem, které byly zadány v roce „1998“. Pro tyto přidružené záznamy počítám celkovou částku objednávky pro každou objednávku tak, že sečtu všechny „“ záznamy pomocí následujícího vzorce: sum(UnitPrice * Quantity * (1-Discount)). Pokud má každá objednávka zákazníka za rok 1998 celkovou částku v dolarech vyšší než 750, vypočítám pak ve vnějším dotazu částku rabatu pomocí tohoto vzorce: „Count(*)*75 „.

Dotazovací strojSQL Serveru provede vnitřní korelovaný poddotaz v klauzuli HAVING pouze pro ty záznamy zákazníků, které byly identifikovány ve vnějším dotazu, neboli v podstatě pouze pro ty zákazníky, kteří zadali objednávky v roce „1998“.

Provedení aktualizačního příkazu pomocí korelovaného poddotazu

Korelovaný poddotaz lze použít i v aktualizačním příkazu. Zde je příklad:

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

Tady je sada výsledků, kterou dostanu, když tento dotaz spustím na svém počítači:

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

V mém dotazu výše jsem použil korelovaný poddotaz k aktualizaci sloupce A v tabulce A součtem sloupce B v tabulce B pro řádky, které mají stejnou hodnotu ve sloupci A jako aktualizovaný řádek.

Závěr

Dovolte mi, abych to shrnul. Poddotaz a korelovaný poddotaz jsou dotazy SELECT zakódované uvnitř jiného dotazu, známého jako vnější dotaz. Související poddotaz a korelovaný poddotaz pomáhají určit výsledek sady výsledků vrácených kompletním dotazem. Poddotaz, je-li proveden nezávisle na vnějším dotazu, vrátí množinu výsledků, a není tedy závislý na vnějším dotazu. Kdežto korelovaný poddotaz nelze provést nezávisle na vnějším dotazu, protože k určení množiny výsledků vrácených korelovaným poddotazem používá jeden nebo více odkazů na sloupce ve vnějším dotazu. Doufám, že nyní rozumíte rozdílům mezi poddotazy a korelovanými poddotazy a jejich použití v kódu jazyka T-SQL.

“ Zobrazit všechny články sloupkaře Gregoryho A. Larsena