Простейший способ сделать рекурсивное самостоятельное объединение?

Каков самый простой способ сделать рекурсивное самосоединение в SQL Server? У меня есть таблица вроде этого:

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

И я хочу, чтобы записи были связаны только с иерархией, начиная с конкретного человека. Итак, если я запросил иерархию CJ с помощью PersonID = 1, я бы получил:

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

Я немного застрял в этом, не могу думать, как это сделать, кроме ответа с фиксированной глубиной, основанного на связке. Это будет делать так, как это происходит, потому что у нас не будет много уровней, но я хотел бы сделать это правильно.

Спасибо! Крис.

Ответ 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

Добавив условие упорядочения, вы можете сохранить порядок дерева:

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, вы можете изменить порядок братьев и сестер.

Ответ 2

Используя 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

Ответ 3

Запрос Quassnoi с изменением для большой таблицы. Родители с большим количеством потомков затем 10: Формирование в виде str (5) 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

Ответ 4

SQL 2005 или более поздняя версия, CTE являются стандартным способом перехода по показанным примерам.

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 [email protected]

    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

(который будет работать в 2005 году, это просто не стандартный способ сделать это. Тем не менее, если вы обнаружите, что более простой способ работы, запустите с ним)

Если вам действительно нужно сделать это в SQL7, вы можете сделать примерно это в sproc, но не можете выбрать из него - SQL7 не поддерживает UDF.