进行递归自联接的最简单方法? [英] Simplest way to do a recursive self-join?

问题描述

在SQL Server中进行递归自联接的最简单方法是什么?我有一张这样的桌子:

What is the simplest way of doing a recursive self-join in SQL Server? I have a table like this:

PersonID | Initials | ParentID
1          CJ         NULL
2          EB         1
3          MB         1
4          SW         2
5          YT         NULL
6          IS         5

我希望能够获取仅与从特定人员开始的层次结构相关的记录.因此，如果我通过PersonID = 1请求CJ的层次结构，我将得到:

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 | Initials | ParentID
1          CJ         NULL
2          EB         1
3          MB         1
4          SW         2

对于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.

谢谢！克里斯.

推荐答案

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

通过更改ORDER BY条件，您可以更改兄弟姐妹的顺序.

By changing the ORDER BY condition you can change the ordering of the siblings.

这篇关于进行递归自联接的最简单方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！