SQL Server: Comment limiter la récursivité CTE aux lignes simplement ajoutées de manière récursive?

Exemple Plus Simple

Essayons un exemple plus simple, afin que les gens peuvent envelopper leurs têtes autour des concepts, et d'avoir un exemple concret que vous pouvez copier&collez-le dans la Requête SQL Analizer:

Imaginer un Nœuds table, avec une hiérarchie:

A
 - B
    - C

Nous pouvons commencer à faire des tests dans la Requête Analizer:

CREATE TABLE ##Nodes
(
 NodeID varchar(50) PRIMARY KEY NOT NULL,
 ParentNodeID varchar(50) NULL
)

INSERT INTO ##Nodes (NodeID, ParentNodeID) VALUES ('A', null)
INSERT INTO ##Nodes (NodeID, ParentNodeID) VALUES ('B', 'A')
INSERT INTO ##Nodes (NodeID, ParentNodeID) VALUES ('C', 'B')

De sortie souhaité:

ParentNodeID    NodeID    GenerationsRemoved
============    ======    ==================
NULL            A         1
NULL            B         2
NULL            C         3
A               B         1
A               C         2
B               C         1

Maintenant le suggère CTE expression, il est incorrect de sortie:

WITH NodeChildren AS
(
   --initialization
   SELECT ParentNodeID, NodeID, 1 AS GenerationsRemoved
   FROM ##Nodes
   WHERE ParentNodeID IS NULL

   UNION ALL

   --recursive execution
   SELECT P.ParentNodeID, N.NodeID, P.GenerationsRemoved + 1
   FROM NodeChildren AS P
      INNER JOIN ##Nodes AS N
      ON P.NodeID = N.ParentNodeID
)
SELECT ParentNodeID, NodeID, GenerationsRemoved
FROM NodeChildren

De sortie réelle:

ParentNodeID    NodeID    GenerationsRemoved
============    ======    ==================
NULL            A         1
NULL            B         2
NULL            C         3

Remarque: Si SQL Server 2005† CTE ne peut pas faire ce que je faisais avant en 2000‡, c'est très bien, et c'est la réponse. Et celui qui donne "il n'est pas possible" que la réponse va gagner le butin. Mais je vais attendre quelques jours pour que tout le monde d'accord est qu'il n'est pas possible avant que je irrecovably donner 250 points de réputation pour une non-solution à mon problème.

Nitpickers Coin

† † 2008

‡sans avoir recours à un UDF*, qui est la solution déjà

*sauf si vous pouvez voir un moyen d'améliorer la performance de l'UDF dans la question d'origine

---

Question D'Origine

j'ai un tableau de Nœuds, chacun avec un parent qui pointe vers un autre Nœud (ou null).

À des fins d'illustration:

1 My Computer
    2 Drive C
         4 Users
         5 Program Files
         7 Windows
             8 System32
    3 Drive D
         6 mp3

je veux une table qui renvoie toutes les relations parent-enfant, et le nombre de générations entre elles

Pour pour tout parent direct de la relation:

ParentNodeID  ChildNodeID  GenerationsRemoved
============  ===========  ===================
(null)        1            1
1             2            1
2             4            1
2             5            1
2             7            1
1             3            1
3             6            1
7             8            1

Mais ensuite il y a les grands-parents de relations:

ParentNodeID  ChildNodeID  GenerationsRemoved
============  ===========  ===================
(null)        2            2
(null)        3            2
1             4            2
1             5            2
1             7            2
1             6            2
2             8            2

Et la il y a la grand-grand-grands-parents de relations:

ParentNodeID  ChildNodeID  GenerationsRemoved
============  ===========  ===================
(null)        4            3
(null)        5            3
(null)        7            3
(null)        6            3
1             8            3

Donc je peux comprendre la base de la CTE d'initialisation:

WITH (NodeChildren) AS
{
   --initialization
   SELECT ParentNodeID, NodeID AS ChildNodeID, 1 AS GenerationsRemoved
   FROM Nodes
} 

Le problème est désormais de la partie récursive. La réponse la plus évidente, bien sûr, ne fonctionne pas:

WITH (NodeChildren) AS
{
   --initialization
   SELECT ParentNodeID, ChildNodeID, 1 AS GenerationsRemoved
   FROM Nodes

   UNION ALL

   --recursive execution
   SELECT parents.ParentNodeID, children.NodeID, parents.Generations+1
   FROM NodeChildren parents
    INNER JOIN NodeParents children
    ON parents.NodeID = children.ParentNodeID
} 

Msg 253, Level 16, State 1, Line 1
Recursive member of a common table expression 'NodeChildren' has multiple recursive references.

Toutes les informations nécessaires pour générer l'ensemble de la liste récursive est présent dans le premier CTE table. Mais si ce n'est pas permis, je vais essayer:

WITH (NodeChildren) AS
{
   --initialization
   SELECT ParentNodeID, NodeID, 1 AS GenerationsRemoved
   FROM Nodes

   UNION ALL

   --recursive execution
   SELECT parents.ParentNodeID, Nodes.NodeID, parents.Generations+1
   FROM NodeChildren parents
    INNER JOIN Nodes
    ON parents.NodeID = nodes.ParentNodeID
} 

Mais qui échoue parce que ce n'est pas seulement le rejoindre sur le récursive éléments, mais conserve recursivly en ajoutant les mêmes lignes:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Dans SQL Server 2000, j'ai simulé un CTE en utilisant une Fonction Définie par l'Utilisateur (UDF):

CREATE FUNCTION [dbo].[fn_NodeChildren] ()
RETURNS @Result TABLE (
    ParentNodeID int NULL,
    ChildNodeID int NULL,
    Generations int NOT NULL) 
AS  
/*This UDF returns all "ParentNode" - "Child Node" combinations
    ...even multiple levels separated
BEGIN 
    DECLARE @Generations int
    SET @Generations = 1

    --Insert into the Return table all "Self" entries
    INSERT INTO @Result
    SELECT ParentNodeID, NodeID, @Generations
    FROM Nodes
    WHILE @@rowcount > 0 
    BEGIN
        SET @Generations = @Generations + 1
        --Add to the Children table: 
        --  children of all nodes just added 
        -- (i.e. Where @Result.Generation = CurrentGeneration-1)
        INSERT @Result
        SELECT CurrentParents.ParentNodeID, Nodes.NodeID, @Generations
        FROM Nodes
            INNER JOIN @Result CurrentParents
            ON Nodes.ParentNodeID = CurrentParents.ChildNodeID
        WHERE CurrentParents.Generations = @Generations - 1
    END
    RETURN
END

Et de la magie pour l'empêcher de faire exploser a été la limitation de la clause where:
OÙ CurrentParents.Générations - @Générations-1

Comment empêcher une expression de table commune récursive à partir de recursing pour toujours?

source d'informationauteur Ian Boyd