¿La forma más sencilla de hacer un auto-join recursivo?
¿Cuál es la forma más sencilla de hacer un auto-join recursivo en SQL Server? Tengo una tabla como esta:
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
5 YT NULL
6 IS 5
Y quiero poder obtener los registros solo relacionados con una jerarquía comenzando con una persona específica. Así que si solicitara 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 por EB obtendría:
PersonID | Initials | ParentID
2 EB 1
4 SW 2
Estoy un poco atascado en esta lata no puedo pensar cómo hacerlo aparte de una respuesta de profundidad fija basada en un montón de uniones. Esto haría lo que sucede porque nosotros no tendrá muchos niveles, pero me gustaría hacerlo correctamente.
Gracias! Chris.
5 answers
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
Cambiando la condición ORDER BY
puede cambiar el orden de los hermanos.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-11-18 16:41:27
Usando CTEs 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-11-18 16:37:59
La consulta Quassnoi con un cambio para tabla grande. Padres con más hijos que 10: Formateando como str (5) el row_number ()
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-11-18 17:42:15
SQL 2005 o posterior, los CTE son el camino estándar a seguir según los ejemplos mostrados.
SQL 2000, puede hacerlo usando UDFs -
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 usted encuentra que la forma más fácil de trabajar, ejecutar con él)
Si realmente necesita hacer esto en SQL7, puede hacer aproximadamente lo anterior en un sproc pero no pudo seleccionar de él: SQL7 no admite UDFs.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-11-18 17:18:06
Compruebe lo siguiente para ayudar a entender el concepto de recursión CTE
DECLARE
@startDate DATETIME,
@endDate DATETIME
SET @startDate = '11/10/2011'
SET @endDate = '03/25/2012'
; WITH CTE AS (
SELECT
YEAR(@startDate) AS 'yr',
MONTH(@startDate) AS 'mm',
DATENAME(mm, @startDate) AS 'mon',
DATEPART(d,@startDate) AS 'dd',
@startDate 'new_date'
UNION ALL
SELECT
YEAR(new_date) AS 'yr',
MONTH(new_date) AS 'mm',
DATENAME(mm, new_date) AS 'mon',
DATEPART(d,@startDate) AS 'dd',
DATEADD(d,1,new_date) 'new_date'
FROM CTE
WHERE new_date < @endDate
)
SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-09-28 10:00:02