SQL 进行递归自联接的最简单方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1757260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simplest way to do a recursive self-join?
提问by Chris
What is the simplest way of doing a recursive self-join in SQL Server? I have a table like this:
在 SQL Server 中进行递归自联接的最简单方法是什么？我有一张这样的表：
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
5 YT NULL
6 IS 5
And I want to be able to get the records only related to a hierarchy starting with a specific person. So If I requested CJ's hierarchy by PersonID=1 I would get:
而且我希望能够获取仅与从特定人员开始的层次结构相关的记录。因此，如果我通过 PersonID=1 请求 CJ 的层次结构，我会得到：
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
And for EB's I'd get:
对于 EB，我会得到：
PersonID | Initials | ParentID
2 EB 1
4 SW 2
I'm a bit stuck on this can can't think how to do it apart from a fixed-depth response based on a bunch of joins. This would do as it happens because we won't have many levels but I would like to do it properly.
除了基于一堆连接的固定深度响应之外，我有点无法思考如何做到这一点。这会发生，因为我们不会有很多级别，但我想正确地做到这一点。
Thanks! Chris.
谢谢！克里斯。
回答by Quassnoi
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
By adding the ordering condition, you can preserve the tree order:
通过添加排序条件，您可以保留树顺序：
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
By changing the ORDER BYcondition you can change the ordering of the siblings.
通过更改ORDER BY条件，您可以更改兄弟姐妹的顺序。
回答by Adriaan Stander
Using CTEs you can do it this way
使用 CTE，你可以这样做
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
回答by guille
The Quassnoi query with a change for large table. Parents with more childs then 10: Formating as str(5) the row_number()
对大表进行了更改的 Quassnoi 查询。孩子多于 10 岁的父母：将 row_number() 格式化为 str(5)
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
回答by Premchandra Singh
Check following to help the understand the concept of CTE recursion
检查以下内容以帮助理解 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)
回答by eftpotrm
SQL 2005 or later, CTEs are the standard way to go as per the examples shown.
SQL 2005 或更高版本，CTE 是按照所示示例使用的标准方法。
SQL 2000, you can do it using UDFs -
SQL 2000，您可以使用 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
(which will work in 2005, it's just not the standard way of doing it. That said, if you find that the easier way to work, run with it)
（这将在 2005 年起作用，这不是标准的做法。也就是说，如果您发现更简单的工作方式，请使用它）
If you really need to do this in SQL7, you can do roughly the above in a sproc but couldn't select from it - SQL7 doesn't support UDFs.
如果您真的需要在 SQL7 中执行此操作，您可以在 sproc 中大致执行上述操作，但无法从中选择 - SQL7 不支持 UDF。
