By: Jayendra Viswanathan | Updated: 2018-03-27 | Comments (2) | Related: More > Scripts
Problem
Molte organizzazioni hanno un certo tipo di gerarchia per i processi aziendali. Quando arriva a grandi organizzazioni, la gerarchia può diventare molto complessa e grande, quindi costruire una gerarchia in un RDBMS è un compito noioso. Dobbiamo creare viste, cursori e presto, ma usare un CTE in SQL Server è una soluzione migliore per recuperare i dati basati sulla gerarchia e in questo suggerimento, ti mostrerò come.,
Soluzione
Le espressioni di tabella comuni (CTE) hanno due tipi, ricorsivo e non ricorsivo. Wewill vedere come il CTE ricorsivo funziona con esempi in questo suggerimento.
Un CTE ricorsivo può essere spiegato in tre parti:
- Anchor Query: Questa è la prima istruzione che viene eseguita. Questa interrogazione fornirà i dati di base per il CTE.
- Separatore: Questa è la parte centrale in cui generalmente usiamo un UNION ALLand pochi altri operatori.
- Query ricorsiva: Questa è la parte principale, questa è la query CTE che si riferisce allo stesso CTE per ricorsione.,
Creiamo un esempio di CTE
Prendiamo uno scenario di un grafico di organizzazione (org). In questo esempio il grafico di organizzazione partirebbe da” CEO “e finirebbe al”PurchaseDepartment”. Ogni reparto / persona è collegato al predecessore asnodes. Vediamo come un CTE può essere utilizzato per ottenere questo in SQL Server. Ci sarà alsotouch base su come utilizzare MAXRECURSION quando si utilizza un CTE.
Creiamo una tabella di esempio.
Ora viene creata la tabella e possiamo popolare la tabella con i valori della tabella “MyDepartment”.,Le seguenti istruzioni insert possono essere eseguite per inserire i dati nella tabella “MyDepartment”.
Il ParentID per l’albero più in alto è impostato su NULL che indica che non c’è noparent per questa riga. Ora che abbiamo caricato i dati, possiamo codificare una query per walkthe hierarchy usando un’espressione di tabella comune.
Dobbiamo riferire sull’intera struttura organizzativa sotto il CEO. Il seguente codice ricorsivo che utilizza un CTE ci otterrà l’output:
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
Di seguito è riportato l’output dall’esecuzione della query di cui sopra.,/td>
Anchor Query
Creating tables is one piece of it, inserting data is another group in the example.,La parte importante è implementare il CTE usando la clausola WITH. Per il nostro esempioil nome del CTE è chiamato “OrgTree”. La prima selezione nel cteè utilizzata per estrarre il primo nodo dell’albero che è “CEO” e l’ID del genitore è impostato come NULL. La query seguente otterrà il primo nodo nel nostro esempio.
SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartmentWHERE ParentID IS NULL
| Id_dipartimento | DepartmentName | ParentID | Albero |
|---|---|---|---|
| 1 | , CEO di | NULL | 0 |
di Seguito di spiegare i dati., Il lato sinistro ha i dati padre e il lato destro ha i dati figlio. Se si nota DepartmentID 1 (lato sinistro) è il parentfor DepartmentID (lato destro) 2 e 3. Se si guarda oltre ogni dipartimentol’ID ècollegato con il ParentID nella tabella figlio. Di seguito è riportata la rappresentazione dell’immaginedella query generata sopra. La freccia collega il dipartimentoid e parentiper il nostro riferimento.
Separatore e query ricorsiva
La sezione successiva è il JOIN INTERNO che combina il CTE dove in ricorsion comesint immagine, che interno si riferisce a se stesso., Il JOIN INTERNO recupera i dati dividi la tabella “MyDepartment” in due parti usando OrgTreeCTE e fa un join e crea il CTE per interrogare il CTE.
SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1FROM MyDepartmentJOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
Query Designer
La schermata di Query Designer qui sotto è disponibile per noi per vedere la query inthe Designer. Il lato destro è il CTE – “OrgTree”, poiché CTE verrà creato dopo l’esecuzione il Progettista Query non mostra le colonne, se si nota la tabella” MyDepartment ” ha la colonna e il riferimento di JOIN INTERNO.,
MAXRECURSION
Quando si tratta di utilizzare un CTE uno dei problemi affrontati è un ciclo infinito mentre si forma il CTE. In generale, quando la query padre e figlio restituisce lo stesso valore orequal, il CTE può andare in un ciclo infinito e la transazione può andare in un ciclo infinito. Per evitare ciò esiste una clausola option che può essere utilizzata alla fine del comando CTE SELECT con la parola chiave MAXRECURSION e il conteggio delle righe. Using0 non ha restrizioni, ma nel nostro esempio ho usato un valore di 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)
Query di esempio
Prova queste query di esempio per trovare i dati e vedere se riesci a trovare otherscenarios e come interrogare i dati.
-- 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;
Passi successivi
- I CTES sono una potente funzionalità in SQL Server. La sintassi è facile da usare e possiamo costruire qualsiasi tipo di tabelle gerarchiche e dati in base alle nostre esigenze utilizzando un CTE.,
- Prendere uno sguardo ad alcuni di questi suggerimenti:
- Query Ricorsive utilizzando Espressioni di Tabella Comuni (CTE) in SQL Server
- utilizzo di SQL Server Cte per rendere il vostro codice T-SQL, leggibile dagli esseri umani
- SQL Server CTE e XML Ricorsione Script
Ultimo Aggiornamento: 2018-03-27
Circa l’autore
Jayendra è un Project Leader con molti anni di esperienza., Ha una forte conoscenza nello sviluppo di software e nella gestione dei progetti.Visualizza tutti i miei suggerimenti
- Altri suggerimenti per gli sviluppatori di database…
