De: sa jayendra Viswanathan | Actualizat: 2018-03-27 | Comentarii (2) | Conexe: Mai > Script-uri
Probleme
Multe organizații au un anumit tip de ierarhie pentru procesele de afaceri. Când vine la organizații mari, ierarhia poate deveni foarte complexă și mare, astfel încât construirea unei ierarhii într-un RDBMS este o sarcină obositoare. Trebuie să creăm vizualizări, cursoare și în curând, dar utilizarea unui CTE în SQL Server este o soluție mai bună pentru a prelua date bazate pe ierarhie și în acest sfat, vă voi arăta cum.,
soluție
expresii comune de masă (CTE) au două tipuri, recursive și non-recursive. Vom vedea cum funcționează CTE recursiv cu exemple din acest sfat.
un CTE recursiv poate fi explicat în trei părți:
- Anchor Query: aceasta este prima declarație care este executată. Această interogareva oferi datele de bază pentru CTE.
- Separator: aceasta este partea de mijloc în care, în general, folosim o uniune ALLand câțiva operatori.
- interogare recursivă: aceasta este partea principală, aceasta este interogarea CTE care se referă la același CTE prin recursivitate.,
să creăm un exemplu de CTE
să luăm un scenariu al unei diagrame a organizației (org). În acest exemplu, graficul de organizare ar începe de la” CEO „și ar ajunge la”PurchaseDepartment”. Fiecare departament / persoană este legată de predecesorul asnodes. Să vedem cum poate fi folosit un CTE pentru a realiza acest lucru în SQL Server. Vom baza alsotouch pe modul de utilizare a MAXRECURSION atunci când se utilizează un CTE.
să creăm un tabel de probă.acum, tabelul este creat și putem popula tabelul cu valori tabelul „MyDepartment”.,Instrucțiunile de inserare de mai jos pot fi executate pentru a insera datele în tabelul „MyDepartment”.
ParentID pentru arborele cel mai de sus este setat la NULL, indicând faptul că nu există noparent pentru acest rând. Acum că am încărcat datele, putem codifica o interogare pentru a merge pe ierarhie folosind o expresie comună a tabelului. trebuie să raportăm întreaga structură organizațională sub CEO. Următorul cod recursiv folosind un CTE ne va obține ieșirea:
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID) SELECT * FROM OrgTree ORDER BY Tree
mai jos este ieșirea din execuția interogării de mai sus.,/td>
Anchor Query
Creating tables is one piece of it, inserting data is another group in the example.,Partea importantă este implementarea CTE folosind clauza WITH. Pentru exemplul nostrunumele CTE este numit „OrgTree”. Prima selectare din Cteeste folosit pentru a extrage primul nod al arborelui care este „CEO” și ID-ul parent este setat ca nul. Interogarea de mai jos va primi primul nod în exemplul nostru.
SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartmentWHERE ParentID IS NULL
| DepartmentID | DepartmentName | ParentID | Bradul |
|---|---|---|---|
| 1 | CEO-ul | NULL | 0 |
mai Jos voi explica de date., Partea stângă are datele părintești și partea dreaptălateral are datele copilului. Dacă observați DepartmentID 1 (partea stângă) este parentfor DepartmentID (partea dreaptă). 2 și 3. Dacă te uiți mai departe fiecare Departamentd esteconectat cu părintele din tabelul copilului. Mai jos este reprezentarea imaginiidin interogarea generată mai sus. Săgeata conectează Departamentuld și Parentidpentru referința noastră.
Separator și Recursive Query
secțiunea următoare este INNER JOIN combinarea CTE în cazul în care în recursivitate comesinto imagine, care stagiarul se referă la sine., Inner JOIN preia datele bysplitting tabelul „MyDepartment” în două părți folosind OrgTreeCTE și face o alăture și creează CTE pentru noi pentru a interoga CTE.
SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1FROM MyDepartmentJOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
designer de interogare
ecranul de imprimare al designerului de interogare de mai jos este disponibil pentru a vedea interogarea în Designer. Partea dreaptă este CTE – „OrgTree”, din CTE va fi creat după executarea Interogării Designer nu arată coloane, dacă younotice de „am înțeles” masă a coloanei și INNER JOIN referință.,
MAXRECURSION
atunci Când vine vorba de utilizarea unui CTE una dintre problemele cu care se confruntă este o buclă infinită whileforming la CTE. În general, atunci când interogarea părinte și copil returnează aceeași valoare sauqual, CTE poate intra într-o buclă infinită, iar tranzacția poate intra într-o buclă infinită. Pentru a evita acest lucru, există o clauză de opțiune care poate fi utilizată la sfârșitul comenzii CTE SELECT cu cuvântul cheie MAXRECURSION și numărul de rânduri. Utilizarea 0 nu are nicio restricție, dar în exemplul nostru am folosit o valoare de 10.,
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID, 0 AS Tree FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID, OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID)SELECT * FROM OrgTree OPTION (MAXRECURSION 10)
Exemplu Interogările
Încercați aceste exemple de interogări pentru a găsi datele și a vedea dacă puteți veni cu otherscenarios și cum pentru a interoga baza de date.
-- return everyone under Program Manager (ParentID = 8)WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE ParentID = 8 UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID)SELECT * FROM OrgTree;-- return Vice President (DepartmentID = 4) and direct reports (ParentID = 4)WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE DepartmentID = 4 UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID WHERE MyDepartment.ParentID = 4)SELECT * FROM OrgTree; -- return everyone above Senior Manager (DepartmentID = 6)WITH OrgTree(DepartmentName,ParentID,ReportsTo)AS( SELECT T1.DepartmentName,T2.DepartmentID,T2.DepartmentName FROM MyDepartment T1 INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID WHERE T1.DepartmentID=6 UNION ALL SELECT OT.ReportsTo,T2.DepartmentID,T2.DepartmentName FROM OrgTree OT INNER JOIN MyDepartment T1 ON OT.ParentID=T1.DepartmentID INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID)SELECT * FROM OrgTree;-- return list with of people with no direct reportsWITH OrgTree(ParentID, DepartmentID, DepartmentName, DepartmentLevel) AS ( SELECT ParentID, DepartmentID, DepartmentName, 0 AS DepartmentLevell FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT e.ParentID, e.DepartmentID, e.DepartmentName, DepartmentLevel + 1 FROM MyDepartment AS e INNER JOIN OrgTree AS d ON e.ParentID = d.DepartmentID )SELECT * FROM OrgTree WHERE DepartmentLevel = 5;
Următorii Pași
- CTEs sunt o caracteristică puternic în SQL Server. Sintaxa este ușor de utilizat și putem construi orice fel de tabele și date ierarhice bazate pe nevoile noastre folosindun CTE.,
- să Ia o privire la unele dintre aceste alte sfaturi:
- Interogări Recursive folosind Masă Comună Expresii (CTE) în SQL Server
- Cum să utilizați SQL Server CTEs pentru a face dvs. de cod T-SQL poate fi citit de oameni
- SQL Server CTE și XML Recursivitate Script
Ultima Actualizare: 2018-03-27
Despre autor
Sa jayendra este un Lider de Proiect, cu mulți ani de experiență., El are cunoștințe puternice în dezvoltarea de software și management de proiect.Vezi toate sfaturile mele
- Mai multe sfaturi pentru dezvoltatori de baze de date…
