递归自联接的最简单方法?

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.

谢谢!克里斯。

4 个解决方案

#1

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 BY condition you can change the ordering of the siblings.

通过按条件更改顺序，您可以更改兄弟姐妹的顺序。

#2

Using CTEs you can do it this way

使用CTEs，你可以这样做

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

#3

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

#4

SQL 2005 or later, CTEs are the standard way to go as per the examples shown.

SQL 2005或以后，CTEs是按照所显示的示例进行的标准方法。

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。