Consulta jerárquica única para todos los antepasados / padres e hijos(DB2/SQLServer)
sql-server hierarchical (3)
Encontré la solución para Oracle usando UNION ALL en dos consultas jerárquicas CONNECT BY, una buscando a los antepasados y otra a los niños.
Quiero lograr lo mismo para DB2 y SQL Server .
Sé que un elemento podría ser una raíz, una rama o una hoja en la jerarquía. Necesito buscar toda su jerarquía.
Supongamos que tengo itemid = ''item3'' y class = ''my class'' , necesito encontrar sus ancestros e hijos, se me ocurrió:
with ancestor (class, itemid, parent, base, depth)
as (
select root.class, root.itemid, root.parent, root.itemid, 0
from item root
where root.class = ''myclass''
and root.itemid = ''item3''
-- union all
-- select child.class, child.itemid, child.parent, root.base, root.depth+1
-- from ancestor root, item child
-- where child.class = root.class
-- and child.parent = root.itemid
union all
select parent.class, parent.itemid, parent.parent, parent.itemid, root.depth-1
from ancestor root, item parent
where parent.class = root.class
and parent.itemid = root.parent
)
select distinct class, itemid, parent, base, depth
from ancestor
order by class, base, depth asc, itemid
Quiero un resultado como este:
class itemid parent base depth
myclass item1 null item3 -2
myclass item2 item1 item3 -1
myclass item3 item2 item3 0
myclass item4 item3 item3 1
myclass item5 item5 item3 2
Si se ejecuta el SQL anterior obtengo los ancestros bien. Ahora si elimino los comentarios, parece estar en un bucle infinito. Debe haber una manera de hacer que eso funcione.
Puedo obtener los resultados en jerarquía de una dirección (antecesor o hijos) bien, pero no puedo obtener ambos en una sola consulta.
¿Alguna vez alguien intentó algo así?
Gracias
En la consulta comentada, debes mencionar que parent.parent es NULL
Por favor revisa este http://msdn.microsoft.com/en-us/library/ms186243.aspx
USE AdventureWorks2008R2; GO WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level) AS (
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID )
-- Statement that executes the CTE SELECT ManagerID, EmployeeID, Title, DeptID, Level FROM DirectReports INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID WHERE dp.GroupName = N''Sales and Marketing'' OR Level = 0; GO
Si no te importa hacerlo usando dos instrucciones WITH , el siguiente devuelve todo tu árbol de jerarquía.
Datos de prueba
DECLARE @item TABLE (
class VARCHAR(32)
, itemid VARCHAR(32)
, parent VARCHAR(32)
)
INSERT INTO @item VALUES
(''myclass'', ''item1'', null)
, (''myclass'', ''item2'', ''item1'')
, (''myclass'', ''item3'', ''item2'')
, (''myclass'', ''item4'', ''item3'')
, (''myclass'', ''item5'', ''item4'')
Declaración de SQL
;WITH children AS (
SELECT class
, itemid
, parent
, base = itemid
, depth = 0
FROM @item
WHERE class = ''myclass''
AND itemid = ''item3''
UNION ALL
SELECT children.class
, i.itemid
, i.parent
, children.base
, children.depth + 1
FROM children
INNER JOIN @item i ON i.parent = children.itemid
AND i.class = children.class
)
, parents AS (
SELECT *
FROM children
WHERE depth = 0
UNION ALL
SELECT parents.class
, i.itemid
, i.parent
, parents.base
, parents.depth - 1
FROM parents
INNER JOIN @item i ON i.itemid = parents.parent
AND i.class = parents.class
)
SELECT *
FROM children
UNION
SELECT *
FROM parents
ORDER BY depth
use 3 CTE
with
ancestors as (...)
,children as (...)
,all_ as (select * from ancestors union all select * from children)
select * from all_