Le moyen le plus simple de faire une auto-jointure récursive dans SQL Server?

Question

Quelle est la façon la plus simple de faire un appel récursif à l'auto-jointure dans SQL Server? J'ai un tableau comme ceci:

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

Et je veux être en mesure d'obtenir les enregistrements uniquement liée à une hiérarchie qui commence avec une personne en particulier. Donc, Si j'ai demandé à CJ de la hiérarchie par PersonID=1 je voudrais obtenir:

PersonID | Initials | ParentID
1          CJ         NULL
2          EB         1
3          MB         1
4          SW         2

Et pour EB est que j'aimerais obtenir:

PersonID | Initials | ParentID
2          EB         1
4          SW         2

Je suis un peu coincé sur ce peut ne peut pas penser comment le faire en dehors d'une profondeur fixe de réponse basé sur un tas de jointures. Ce serait faire comme cela arrive parce que nous n'aurons pas beaucoup de niveaux, mais je voudrais le faire correctement.

Merci! Chris.

Answer 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
 

En ajoutant la condition de commande, vous pouvez préserver l'ordre de l'arbre:

 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
 

En modifiant la condition ORDER BY , vous pouvez modifier l'ordre des frères et sœurs.

Answer 2

En utilisant les CTE, vous pouvez le faire de cette façon

 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
 

Answer 3

Le Quassnoi requête avec une grande table. Les Parents ayant plus d'enfant à 10: Formatage str(5) la fonction row_number()

AVEC q COMME 
(
 SÉLECTIONNEZ m.*, CAST(str(ROW_NUMBER() OVER (ORDER BY m.ordernum),5), VARCHAR(MAX)) COLLATE Latin1_General_BIN COMME bc
 DE #t m
 OÙ ParentID =0
 L'UNION DE TOUS
 SÉLECTIONNEZ m.*, q.bc + '.' + str(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDRE PAR m.ordernum),5) COLLATE Latin1_General_BIN
 DE #t m
 REJOIGNEZ q
 SUR m.parentID = q.DBID
)
SÉLECTIONNEZ *
À PARTIR de q
COMMANDE PAR
bc

Answer 4

SQL 2005 ou version ultérieure, les CTE sont le moyen standard d’utiliser les exemples présentés.

SQL 2000, vous pouvez le faire en utilisant des 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
 

(ce qui fonctionnera en 2005, ce n’est tout simplement pas la façon habituelle de le faire. Cela dit, si vous trouvez que la façon la plus facile de travailler est de fonctionner avec elle)

Si vous avez vraiment besoin de faire cela dans SQL7, vous pouvez effectuer à peu près ce qui précède dans un sproc mais vous ne pouvez pas en sélectionner un - SQL7 ne prend pas en charge les fichiers UDF.

Answer 5

S'il s'agit bien de SQL 2005+, vous pouvez utiliser les CTE

Google "données récursives hiérarchiques SQL 2005"

ou voir ce lien