在SQL Server中进行递归自联接的最简单方法是什么?我有一个这样的表:
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
5 YT NULL
6 IS 5
而且我希望能够获得仅与从特定人员开始的层次结构相关的记录。因此,如果我通过PersonID = 1请求CJ的层次结构,则会得到:
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
对于EB,我会得到:
PersonID | Initials | ParentID
2 EB 1
4 SW 2
除了基于一堆联接的固定深度响应之外,我对此深感困惑。这样做会发生,因为我们没有很多级别,但我想正确地做。
谢谢!克里斯。
最佳答案
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
通过添加排序条件,可以保留树的顺序:
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
通过更改
ORDER BY
条件,您可以更改 sibling 的顺序。