Utilizarea unei subinterogări corelate într-o instrucțiune T-SQL

iunie 2, 2021admin

În articolul de luna trecută, am discutat despre ce și cum să folosim o subinterogare într-o instrucțiune T-SQL. Luna aceasta voi dezvolta acest subiect discutând despre subinterogările corelate. Voi explica ce este o subîntrebare corelată și voi arăta o serie de exemple diferite privind modul de utilizare a unei subîntrebări într-o instrucțiune T-SQL.

Ce este o subîntrebare corelată?

O subîntrebare corelată este o instrucțiune SELECT aninată în interiorul unei alte instrucțiuni T-SQL, care conține o referință la una sau mai multe coloane din interogarea exterioară. Prin urmare, se poate spune că subinterogarea corelată este dependentă de interogarea exterioară. Aceasta este principala diferență dintre o subinterogare corelată și o subinterogare simplă. O subinterogare simplă nu depinde de interogarea externă, poate fi executată independent de interogarea externă și va returna un set de rezultate. O interogare secundară corelată, deoarece este dependentă de interogarea externă, va returna erori de sintaxă dacă este executată de una singură.

O interogare secundară corelată va fi executată de mai multe ori în timpul procesării instrucțiunii T-SQL care conține interogarea secundară corelată. Subinterogarea corelată va fi executată o dată pentru fiecare rând candidat selectat de interogarea externă. Coloanele interogării externe, la care se face referire în subinterogarea corelată, sunt înlocuite cu valorile din rândul candidat înainte de fiecare execuție. În funcție de rezultatele execuției subinterogării corelate, se va determina dacă rândul din interogarea externă este returnat în setul final de rezultate.

Utilizarea unei subinterogări corelate într-o clauză WHERE

Să presupunem că doriți un raport cu toate „OrderID’s” în care clientul nu a achiziționat mai mult de 10% din cantitatea medie vândută pentru un anumit produs. În acest fel, ați putea să revizuiți aceste comenzi și, eventual, să contactați clienții pentru a vă ajuta să determinați dacă a existat un motiv pentru comanda cu cantitate redusă. O subinterogare corelată într-o clauză WHERE vă poate ajuta să produceți acest raport. Iată o instrucțiune SELECT care produce lista dorită de „OrderID’s”:

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

Subinterogarea corelată din comanda de mai sus este conținută în paranteza care urmează semnului greater than din clauza WHERE de mai sus. Aici puteți vedea că această subîntrebare corelată conține o referință la „OD.ProductID”. Această referință compară „ProductID” din interogarea externă cu „ProductID” din interogarea internă. Atunci când această interogare este executată, motorul SQL va executa interogarea interioară, subinterogarea corelată, pentru fiecare înregistrare „”. Această interogare internă va calcula „cantitatea” medie pentru un anumit „ProductID” pentru rândul candidat care este procesat în interogarea externă. Această subinterogare corelată determină dacă interogarea internă returnează o valoare care îndeplinește condiția din clauza WHERE. Dacă da, rândul identificat de interogarea exterioară este plasat în setul de înregistrări care va fi returnat din instrucțiunea completă T-SQL SELECT.

Codul de mai jos este un alt exemplu care utilizează o subinterogare corelată în clauza WHERE pentru a afișa primii doi clienți, pe baza sumei în dolari asociată comenzilor lor, pe regiune. S-ar putea să doriți să efectuați o astfel de interogare, astfel încât să puteți recompensa acești clienți, deoarece ei cumpără cel mai mult pe regiune.

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

Aici puteți vedea că interogarea interioară este o subinterogare corelată, deoarece face referire la „OuterC”, care este aliasul de tabel pentru tabelul „Northwind.DBO.Customer” din interogarea exterioară. Această interogare internă utilizează valoarea „Region” pentru a calcula primii doi clienți pentru regiunea asociată cu rândul care este procesat din interogarea externă. Dacă „CustomerID” din interogarea exterioară este unul dintre primii doi clienți, atunci înregistrarea este plasată în setul de înregistrări care va fi returnat.

Subinterogare corelată în clauza HAVING

Să spunem că organizația dumneavoastră dorește să deruleze un program de stimulare pe parcursul unui an pentru a crește veniturile. Prin urmare, le anunță clienților dumneavoastră că, dacă fiecare comandă pe care o plasează, pe parcursul anului, este mai mare de 750 de dolari, le veți oferi o reducere la sfârșitul anului în valoare de 75 de dolari pentru fiecare comandă pe care o plasează. Mai jos este un exemplu de calculare a sumei reducerii. Acest exemplu utilizează o subinterogare corelată în clauza HAVING pentru a identifica clienții care se califică pentru a primi reducerea. Iată codul meu pentru această interogare:

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 )

Prin revizuirea acestei interogări, puteți vedea că folosesc o interogare corelată în clauza HAVING pentru a calcula suma totală a comenzii pentru fiecare comandă a clientului. Folosesc „CustomerID” din interogarea exterioară și anul comenzii „Datepart(yy,OrderDate)”, pentru a ajuta la identificarea înregistrărilor de comenzi asociate cu fiecare client, care au fost plasate în anul „1998”. Pentru aceste înregistrări asociate, calculez valoarea totală a comenzii, pentru fiecare comandă, prin însumarea tuturor înregistrărilor „”, folosind următoarea formulă: sum(UnitPrice * Quantity * (1-Discount)). În cazul în care fiecare comandă pentru un client, pentru anul 1998, are o valoare totală în dolari mai mare de 750, calculez apoi valoarea rabatului în interogarea exterioară folosind această formulă „Count(*)*75”.

Motorul de interogare al serverului SQL va executa subinterogarea interioară corelată din clauza HAVING numai pentru acele înregistrări de clienți identificate în interogarea exterioară sau, practic, numai pentru acei clienți care au plasat comenzi în „1998”.

Executarea unei declarații de actualizare utilizând o subinterogare corelată

O subinterogare corelată poate fi utilizată chiar și într-o declarație de actualizare. Iată un exemplu:

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

Iată setul de rezultate pe care îl obțin atunci când execut această interogare pe calculatorul meu:

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

În interogarea mea de mai sus, am folosit subinterogarea corelată pentru a actualiza coloana A din tabelul A cu suma coloanei B din tabelul B pentru rândurile care au aceeași valoare în coloana A ca și rândul care se actualizează.

Concluzie

Lasă-mă să rezum. O subinterogare și o subinterogare corelată sunt interogări SELECT codificate în interiorul unei alte interogări, cunoscută sub numele de interogare exterioară. Subinterogarea corelată și subinterogarea ajută la determinarea rezultatului setului de rezultate returnat de interogarea completă. O subinterogare, atunci când este executată independent de interogarea exterioară, va returna un set de rezultate și, prin urmare, nu depinde de interogarea exterioară. În schimb, o subinterogare corelată nu poate fi executată independent de interogarea exterioară, deoarece utilizează una sau mai multe referințe la coloane din interogarea exterioară pentru a determina setul de rezultate returnat de subinterogarea corelată. Sper că acum ați înțeles diferența dintre subinterogări și subinterogări corelate și modul în care acestea pot fi utilizate în codul dvs. T-SQL.

„” Vezi toate articolele editorialistului Gregory A. Larsen