¿La forma más sencilla de realizar una autounión recursiva?
¿Cuál es la forma más sencilla de realizar una autounión recursiva en SQL Server?
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
5 YT NULL
6 IS 5
Quiero poder obtener los registros solo relacionados con una jerarquía que comienza con una persona específica. Entonces, si solicité la jerarquía de CJ por PersonID=1 obtendría:
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
Y para EB obtendría:
PersonID | Initials | ParentID
2 EB 1
4 SW 2
No se me ocurre cómo hacerlo aparte de una respuesta de profundidad fija basada en un montón de uniones. Esto serviría como sucede porque no tendremos muchos niveles pero me gustaría hacerlo correctamente.
WITH q AS
(
SELECT *
FROM mytable
WHERE ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
UNION ALL
SELECT m.*
FROM mytable m
JOIN q
ON m.parentID = q.PersonID
)
SELECT *
FROM q
Al agregar la condición de orden, puede preservar el orden del árbol:
WITH q AS
(
SELECT m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
FROM mytable m
WHERE ParentID IS NULL
UNION ALL
SELECT m.*, q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
FROM mytable m
JOIN q
ON m.parentID = q.PersonID
)
SELECT *
FROM q
ORDER BY
bc
Al cambiar la ORDER BYcondición puedes cambiar el orden de los hermanos.
Usando CTE puedes hacerlo de esta manera
DECLARE @Table TABLE(
PersonID INT,
Initials VARCHAR(20),
ParentID INT
)
INSERT INTO @Table SELECT 1,'CJ',NULL
INSERT INTO @Table SELECT 2,'EB',1
INSERT INTO @Table SELECT 3,'MB',1
INSERT INTO @Table SELECT 4,'SW',2
INSERT INTO @Table SELECT 5,'YT',NULL
INSERT INTO @Table SELECT 6,'IS',5
DECLARE @PersonID INT
SELECT @PersonID = 1
;WITH Selects AS (
SELECT *
FROM @Table
WHERE PersonID = @PersonID
UNION ALL
SELECT t.*
FROM @Table t INNER JOIN
Selects s ON t.ParentID = s.PersonID
)
SELECT *
FROm Selects
La consulta de Quassnoi con un cambio para tabla grande. Padres con más hijos de 10: formatear como str(5) el número_fila()
WITH q AS
(
SELECT m.*, CAST(str(ROW_NUMBER() OVER (ORDER BY m.ordernum),5) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
FROM #t m
WHERE ParentID =0
UNION ALL
SELECT m.*, q.bc + '.' + str(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.ordernum),5) COLLATE Latin1_General_BIN
FROM #t m
JOIN q
ON m.parentID = q.DBID
)
SELECT *
FROM q
ORDER BY
bc
SQL 2005 o posterior, los CTE son la forma estándar de hacerlo según los ejemplos que se muestran.
SQL 2000, puede hacerlo utilizando UDF:
CREATE FUNCTION udfPersonAndChildren
(
@PersonID int
)
RETURNS @t TABLE (personid int, initials nchar(10), parentid int null)
AS
begin
insert into @t
select * from people p
where personID=@PersonID
while @@rowcount > 0
begin
insert into @t
select p.*
from people p
inner join @t o on p.parentid=o.personid
left join @t o2 on p.personid=o2.personid
where o2.personid is null
end
return
end
(que funcionará en 2005, simplemente no es la forma estándar de hacerlo. Dicho esto, si encuentra que esa es la forma más fácil de trabajar, ejecútela)
Si realmente necesita hacer esto en SQL7, puede hacer aproximadamente lo anterior en un sproc pero no puede seleccionarlo: SQL7 no admite UDF.
