Redgate Hub (Română)

introdus în SQL Server 2005, expresia tabelului comun (CTE) este un set de rezultate numit temporar pe care îl puteți face referire într-o declarație de selectare, inserare, actualizare sau ștergere. Puteți utiliza, de asemenea, un CTE într-o declarație creare vizualizare, ca parte a interogării SELECT a vizualizării. În plus, începând cu SQL Server 2008, puteți adăuga un CTE la noua Declarație de îmbinare.SQL Server suportă două tipuri de CTEs-recursive și nonrecursive. În acest articol, vă explic cum să creați ambele tipuri., Exemplele pe care le ofer se bazează pe o instanță locală a SQL Server 2008 și de a prelua date din Baza de date eșantion AdventureWorks2008.

lucrul cu expresii comune de tabel

definiți CTEs adăugând o clauză cu direct înainte de a selecta, insera, actualiza, șterge, sau MERGE declarație.,”>

1 2 3 4 5

] <common_table_expression>::= cte_name )] AS (cte_query)

…which can be represented like this…

As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas., În plus, pentru fiecare CTE, trebuie să furnizați un nume, cuvântul cheie AS și o instrucțiune SELECT. De asemenea, puteți furniza nume de coloane (separate prin virgule), atâta timp cât numărul de nume se potrivește cu numărul de coloane returnate de setul de rezultate.

instrucțiunea SELECT din interogarea CTE trebuie să respecte aceleași cerințe ca cele utilizate pentru crearea unei vizualizări. Pentru detalii despre aceste cerințe, consultați subiectul „creare vizualizare (Transact-SQL)” din SQL Server Books Online. Pentru mai multe detalii despre CTEs în general, consultați subiectul ” cu common_table_expression (Transact-SQL).,”

după ce definiți clauza WITH cu CTEs necesare, puteți face referire la aceste CTEs ca la orice alt tabel. Cu toate acestea, puteți face referire la un CTE numai în sfera de execuție a declarației care urmează imediat clauza WITH. După ce ați rulat declarația, setul de rezultate CTE nu mai este disponibil pentru alte declarații.

crearea unei expresii de tabel comune Nonrecursive

Un CTE nonrecursiv este unul care nu se face referire în cadrul CTE. CTEs Nonrecursive tind să fie mai simple decât CTEs recursive, motiv pentru care încep cu acest tip.,cteTotalSales:

După ce am specificați CTE numele, am oferi două nume de coloane, SalesPersonID și NetSales, care sunt închise în paranteze și separate prin virgulă. Aceasta înseamnă că setul de rezultate returnat de interogarea CTE trebuie să returneze două coloane.

apoi, furnizez cuvântul cheie AS, apoi un set de paranteze care anexează interogarea CTE. În acest caz, declarația SELECT returnează vânzările totale pentru fiecare persoană de vânzări (vânzări totale grupate după ID-ul agentului de vânzări)., După cum puteți vedea, interogarea CTE poate include funcții Transact-SQL, grup de clauze, sau orice elemente care instrucțiunea SELECT într-o definiție vedere poate include.

acum pot face referire la cteTotalSales în declarația care urmează imediat. Pentru acest exemplu, creez o declarație selectată care se alătură vânzărilor.vSalesPerson vedere la cteTotalSales, pe baza ID-ul agent de vânzări. Apoi trag numele și locațiile din vizualizare și vânzările nete de la CTE. Următorul tabel prezintă rezultatele returnate de această declarație.,

după Cum ați văzut mai devreme, în sintaxă, puteți include mai multe CTEs într-o CU clauza.,

prima CTE-cteTotalSales-este similară cu cea din exemplul precedent, cu excepția faptului că în cazul în CARE clauza a fost calificat mai departe pentru a include vânzările numai din 2003. După ce definesc cteTotalSales, adaug o virgulă și apoi definesc cteTargetDiff, care calculează diferența dintre totalul vânzărilor și cota de vânzări.

noul CTE definiție specifică trei coloane pentru setul de rezultate: SalesPersonID, SalesQuota, și QuotaDiff., După cum v-ați aștepta, interogarea CTE returnează trei coloane. Primul este ID-ul agentului de vânzări. Al doilea este cota de vânzări. Cu toate acestea, deoarece o cotă de vânzări nu este definită pentru unii agenți de vânzări, folosesc o declarație de caz. Dacă valoarea este nulă, această valoare este setată la 0, altfel se utilizează valoarea reală SalesQuota.

coloana finală returnată este diferența dintre vânzările nete și cota de vânzări. Din nou, folosesc o declarație de caz. Dacă valoarea SalesQuota este nulă, atunci se utilizează valoarea NetSales, altfel cota de vânzări este scăzută din vânzările nete pentru a ajunge la diferență.,

ceva interesant de remarcat despre a doua interogare CTE este că m-am alăturat vânzărilor.Tabelul de vânzări la primul CTE-cteTotalSales-așa că am putut calcula diferența dintre vânzările totale și cota de vânzări. Ori de câte ori definiți mai multe CTEs într-o singură clauză cu, puteți face referire la CTEs anterioare (dar nu invers).

odată ce mi-am definit CTEs-urile, le pot face referire în prima afirmație care urmează CTE, așa cum ați văzut în exemplul anterior. În acest caz, mă alătur vânzărilor.,vSalesPerson vedere la cteTotalSales și apoi se alăture la cteTargetDiff, toate bazate pe ID-ul agent de vânzări. Lista mea de selectare Include apoi coloane din toate cele trei surse. Declarația returnează rezultatele prezentate în tabelul următor.după cum puteți vedea, datele de vânzări sunt furnizate pentru toți vânzătorii, inclusiv orașul în care locuiesc, vânzările nete, cota de vânzări și diferența calculată dintre cele două cifre. În acest caz, toată lumea depășește cu mult cota, unde a fost definită o cotă.,

crearea unei expresii Recursive comune a tabelului

Un CTE recursiv este unul care se face referire în cadrul acelui CTE. CTE recursiv este util atunci când se lucrează cu date ierarhice, deoarece CTE continuă să execute până când interogarea returnează întreaga ierarhie.un exemplu tipic de date ierarhice este un tabel care include o listă de angajați. Pentru fiecare angajat, tabelul oferă o referire la managerul acelei persoane. Această referință este ea însăși un ID de angajat în același tabel., Puteți utiliza un CTE recursiv pentru a afișa ierarhia datelor angajaților, așa cum ar apărea în organigrama.rețineți că un CTE creat incorect ar putea intra într-o buclă infinită. Pentru a preveni acest lucru, puteți include indiciul MAXRECURSION în clauza de opțiune din Instrucțiunea primară selectare, inserare, actualizare, ștergere sau îmbinare. Pentru informații despre utilizarea sugestiilor de interogare, consultați subiectul ” sugestii de interogare (Transact-SQL)” din SQL Server Books Online.,

Pentru a demonstra cum recursive CTE lucrări, am folosit următoarele instrucțiuni Transact-SQL pentru a crea și popula Angajați masa în AdventureWorks2008 baza de date:

Ca s-ar putea realiza, AdventureWorks2008 baza de date include deja Umane.Masa angajaților. Cu toate acestea, tabelul utilizează acum tipul de date hierarchyid pentru a stoca date ierarhice, ceea ce ar introduce o complexitate inutilă atunci când încearcă să demonstreze un CTE recursiv. Din acest motiv, mi-am creat propria masă., Cu toate acestea, dacă doriți să încercați un CTE recursiv fără a crea și popula un nou tabel, puteți utiliza baza de date de probe AdventureWorks care a fost livrată cu SQL Server 2005. Resursele Umane.Tabelul angajaților din Baza de date stochează datele într-un mod similar cu tabelul pe care îl creez mai sus.,div>2

după Cum puteți vedea, CTE se întoarce cinci coloane: EmpID, FirstName, LastName, MgrID, și EmpLevel. Coloana EmpLevel se referă la nivelul din ierarhia în care se încadrează angajații. Cel mai înalt nivel al ierarhiei este 1, următorul nivel este 2, urmat de 3 și așa mai departe.

interogarea CTE este ea însăși formată din două declarații SELECT, conectate cu operatorul UNION ALL., O interogare CTE recursivă trebuie să conțină cel puțin doi membri (declarații), conectați de către operatorul UNION ALL, UNION, INTERSECT sau cu excepția operatorului. În acest exemplu, prima instrucțiune SELECT este membrul anchor, iar a doua instrucțiune este membrul recursiv. Toți membrii ancoră trebuie să preceadă membrii recursivi și numai membrii recursivi pot face referire la CTE în sine. În plus, toți membrii trebuie să returneze același număr de coloane cu tipurile de date corespunzătoare.acum, să ne uităm mai atent la declarațiile în sine., Prima declarație, membrul ancoră, preia ID-ul angajatului, prenumele, numele și ID-ul managerului din tabelul angajaților, unde ID-ul managerului este nul. Acesta ar fi angajatul din vârful ierarhiei, ceea ce înseamnă că această persoană nu raportează nimănui. În consecință, valoarea ID-ului managerului este nulă. Pentru a reflecta faptul că această persoană se află în partea de sus a ierarhiei, atribuie o valoare de 1 coloanei EmpLevel.

a doua declarație din interogarea CTE-membrul recursiv-preia, de asemenea, ID-ul de angajat, prenumele, numele și ID-ul de manager pentru angajați în tabelul de angajați., Cu toate acestea, observați că mă alătur tabelului angajaților la CTE în sine. În plus, join se bazează pe ID-ul managerului din tabelul angajaților și pe ID-ul angajatului din CTE. Procedând astfel, CTE va trece prin tabelul angajaților până când returnează întreaga ierarhie.

un alt element pentru a observa despre a doua declarație este că, pentru coloana EmpLevel, am adăuga valoarea 1 la valoarea EmpLevel așa cum apare în CTE. În acest fel, de fiecare dată când declarația trece prin ierarhie, următorul nivel corect este aplicat angajaților la nivel.,

după ce definesc clauza mea cu, creez o instrucțiune SELECT care preia datele din CTE. Rețineți, totuși, că pentru coloana Manager, recuperez numele și prenumele angajatului asociat cu ID-ul managerului din CTE. Acest lucru îmi permite să afișez numele complet al managerului pentru fiecare angajat. Următorul tabel arată setul de rezultate returnat de instrucțiunea SELECT și CTE-ul acesteia.,

după Cum puteți vedea, CTE, dacă recursiv sau nonrecursive, poate fi un instrument util atunci când aveți nevoie pentru a genera temporar seturi care pot fi accesate într-un SELECT, INSERT, UPDATE, DELETE, sau FUZIONA declarație. Într-un anumit sens, un CTE este ca un tabel derivat: nu este stocat ca obiect și este valabil numai în timpul executării instrucțiunii primare. Cu toate acestea, spre deosebire de tabelul derivat, un CTE poate fi referit de mai multe ori într-o interogare și poate fi auto-referențiere. Și cel mai bine, CTEs sunt relativ ușor de implementat.,

ați observat că Bob folosește AdventureWorks2008 mai degrabă decât AdventureWorks. Dacă preferați pentru a rula aceste exemple împotriva AdventureWorks baza de date, mai degrabă decât AdventureWorks2008 baza de date, ar trebui să schimbe BusinessEntityID coloana la SalesPersonID coloana.