By: Jayendra Viswanathan | Updated: 2018-03-27 | Comments (2) | Related: More > Scripts
Problem
Muchas organizaciones tienen algún tipo de jerarquía para los procesos de negocio. Cuando se trata de organizaciones grandes, la jerarquía puede volverse muy compleja y grande, por lo que construir una jerarquía en un RDBMS es una tarea tediosa. Tenemos que crear vistas, Cursores y pronto, pero usar un CTE en SQL Server es una mejor solución para recuperar datos basados en jerarquías y en este consejo, te mostraré cómo.,
solución
Las expresiones comunes de tabla (CTE) tienen dos tipos, recursiva y no recursiva. Veremos cómo funciona el CTE recursivo con ejemplos en este consejo.
un CTE recursivo se puede explicar en tres partes:
- Anchor Query: esta es la primera instrucción que se ejecuta. Esta consulta proporcionará los datos de base para el CTE.
- separador: esta es la parte central donde generalmente usamos una unión ALLand algunos operadores más.
- consulta recursiva: esta es la parte principal, Esta es la consulta CTE que se refiere al mismo CTE por recursión.,
vamos a crear un ejemplo de CTE
vamos a tomar un escenario de un gráfico de organización (org). En este ejemplo, el gráfico de organización comenzaría desde » CEO «y terminaría en»PurchaseDepartment». Cada departamento / persona está vinculado a los asnodes predecesores. Veamos cómo se puede usar un CTE para lograr esto en SQL Server. También hablaremos sobre cómo usar MAXRECURSION cuando se usa un CTE.
Vamos a crear una tabla de ejemplo.
ahora se crea la tabla y podemos rellenar la tabla con valores la tabla «MyDepartment».,Las instrucciones insert siguientes se pueden ejecutar para insertar los datos en la tabla»MyDepartment».
El ParentID para el árbol más alto se establece en NULL, lo que indica que hay noparent para esta fila. Ahora que hemos cargado los datos podemos codificar una consulta para recorrer la jerarquía usando una expresión de tabla común.
necesitamos informar sobre toda la estructura organizacional bajo el CEO. El siguiente código recursivo usando un CTE nos dará la salida:
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
a continuación se muestra la salida de la ejecución de la consulta anterior.,/td>
Anchor Query
Creating tables is one piece of it, inserting data is another group in the example.,La parte importante es la implementación del CTE utilizando la cláusula WITH. Para nuestro ejemplo, el nombre del CTE se denomina «OrgTree». La primera selección en el CTEis se utiliza para extraer el primer nodo del árbol que es «CEO» y el ID del padre se establece como NULL. La siguiente consulta obtendrá el primer nodo en nuestro ejemplo.
SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartmentWHERE ParentID IS NULL
| DepartmentID | DepartmentName | ParentID | Árbol |
|---|---|---|---|
| 1 | CEO | NULL | 0 |
a Continuación voy a explicar los datos., El lado izquierdo tiene los datos principales y el lado derecho tiene los datos secundarios. Si observa que el DepartmentID 1 (lado izquierdo) es el parentopara el DepartmentID (lado derecho) 2 y 3. Si busca más, cada DepartmentID está conectado con el ParentID en la tabla secundaria. A continuación se muestra la representación de la imagen de la consulta que se generó arriba. La flecha conecta el DepartmentID y parentID para nuestra referencia.
separador y consulta recursiva
la siguiente sección es la unión interna que combina el CTE donde en recursion comes into picture, que intern se refiere a sí mismo., La unión interna recupera los datos dividiendo la tabla» MyDepartment » en dos partes usando el OrgTreeCTE y hace una unión y crea el CTE para que consultemos el CTE.
SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1FROM MyDepartmentJOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
Query Designer
la siguiente impresión de pantalla de Query Designer está disponible para que podamos ver la consulta en el diseñador. El lado derecho es el CTE – «OrgTree», ya que CTE se creará después de la ejecución, El diseñador de consultas no muestra las columnas, si notifica que la tabla» MyDepartment » tiene la columna y la referencia de unión interna.,
MAXRECURSION
Cuando se trata de usar un CTE uno de los problemas enfrentados es un bucle infinito mientras se forma el CTE. En general, cuando la consulta padre e hijo devuelve el mismo valor orequal, el CTE puede entrar en un bucle infinito y la transacción puede entrar en un bucle infinito. Para evitar esto hay una cláusula option que se puede usar al final del comando CTE SELECT con la palabra clave MAXRECURSION y el recuento de filas. Using0 no tiene ninguna restricción, pero en nuestro ejemplo he utilizado un valor 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)
Consultas de Ejemplo
Pruebe estas consultas de ejemplo para encontrar los datos y ver si usted puede venir para arriba con otherscenarios y cómo consultar los datos.
-- 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;
los siguientes pasos
- Los Cte son una característica poderosa en SQL Server. La sintaxis es fácil de usar y podemos construir cualquier tipo de tablas jerárquicas y datos basados en nuestras necesidades usando un CTE.,
- echa un vistazo a algunos de estos otros consejos:
- Consultas Recursivas mediante expresión de Tabla Común (CTE) en SQL Server
- Cómo utilizar SQL Server Cte para hacer su T-SQL de código legible por humanos
- SQL Server CTE y XML Recursividad Script
Última actualización: 2018-03-27
Sobre el autor
Jayendra es un Líder de Proyecto con muchos años de experiencia., Tiene un fuerte conocimiento en desarrollo de software y gestión de proyectos.ver todos mis consejos - Más consejos para desarrolladores de bases de datos…
