Comment une expression de table commune Récursive run, ligne par ligne?

Question

Je pense que j'ai le format de Récursive d'expressions de table communes vers le bas assez bien pour en écrire une, mais encore à trouver moi-même frustré de pas de fin que je ne peux pas traiter manuellement une (faire semblant d'être le moteur SQL de moi-même et d'atteindre le résultat fixé avec un stylo et du papier). J'ai trouvé ceci, qui est proche de ce que je cherche, mais pas assez détaillé. Je n'ai pas de problème de suivi par le biais d'un C++ fonction récursive, et de comprendre comment il fonctionne, mais SQL je ne comprends pas pourquoi ou comment le moteur sait s'arrêter. L'ancre et récursive bloc appelée à chaque fois, ou est le point d'ancrage sauté au plus tard itérations? (J'en doute mais je vais essayer à l'expression de ma confusion à propos de la façon dont il semble à sauter partout.) Si l'ancre est appelée à chaque fois, comment l'ancrage de ne pas apparaître plusieurs fois dans le résultat final? J'espère que quelqu'un peut juste faire une pause en bas de la ligne 1 ligne 2, etc. ce qui se passe et ce qui est "en mémoire" comme le jeu de résultats s'accumule.

J'ai pris la liberté de voler mon exemple à partir de cette page, car il semble être le plus facile à comprendre.

    DECLARE @tbl TABLE ( 
     Id INT 
    ,[Name] VARCHAR(20) 
    ,ParentId INT 
    ) 

INSERT INTO @tbl( Id, Name, ParentId ) 
VALUES 
 (1, 'Europe', NULL) 
,(2, 'Asia',   NULL) 
,(3, 'Germany', 1) 
,(4, 'UK',      1) 
,(5, 'China',   2) 
,(6, 'India',   2) 
,(7, 'Scotland', 4) 
,(8, 'Edinburgh', 7) 
,(9, 'Leith', 8) 

; 

WITH  abcd 
    AS ( 
          -- anchor 
        SELECT  id, [Name], ParentID, 
                CAST(([Name]) AS VARCHAR(1000)) AS "Path" 
        FROM    @tbl 
        WHERE   ParentId IS NULL 
        UNION ALL 
          --recursive member 
        SELECT  t.id, t.[Name], t.ParentID, 
                CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) AS "Path" 
        FROM    @tbl AS t 
                JOIN abcd AS a 
                  ON t.ParentId = a.id 
       )
SELECT * FROM abcd 

Answer 1

Pensez à un récursif CTE comme d'une interminable UNION ALL:

WITH    rows AS
        (
        SELECT  *
        FROM    mytable
        WHERE   anchor_condition
        ),
        rows2 AS
        (
        SELECT  *
        FROM    set_operation(mytable, rows)
        ),
        rows3 AS
        (
        SELECT  *
        FROM    set_operation(mytable, rows2)
        ),
        …
SELECT  *
FROM    rows
UNION ALL
SELECT  *
FROM    rows2
UNION ALL
SELECT  *
FROM    rows3
UNION ALL
…

Dans votre cas, ce serait:

WITH    abcd1 AS
        ( 
        SELECT  *
        FROM    @tbl t
        WHERE   ParentId IS NULL 
        ),
        abcd2 AS
        ( 
        SELECT  t.*
        FROM    abcd1
        JOIN    @tbl t
        ON      t.ParentID = abcd1.id
        ),
        abcd3 AS
        ( 
        SELECT  t.*
        FROM    abcd2
        JOIN    @tbl t
        ON      t.ParentID = abcd2.id
        ),
        abcd4 AS
        ( 
        SELECT  t.*
        FROM    abcd3
        JOIN    @tbl t
        ON      t.ParentID = abcd3.id
        ),
        abcd5 AS
        ( 
        SELECT  t.*
        FROM    abcd4
        JOIN    @tbl t
        ON      t.ParentID = abcd4.id
        ),
        abcd6 AS
        ( 
        SELECT  t.*
        FROM    abcd5
        JOIN    @tbl t
        ON      t.ParentID = abcd5.id
        )
SELECT  *
FROM    abcd1
UNION ALL
SELECT  *
FROM    abcd2
UNION ALL
SELECT  *
FROM    abcd3
UNION ALL
SELECT  *
FROM    abcd4
UNION ALL
SELECT  *
FROM    abcd5
UNION ALL
SELECT  *
FROM    abcd6

Depuis abcd6 ne donne aucun résultat, ce qui implique une condition d'arrêt.

Théoriquement, un appel récursif CTE peut être infini, mais pratiquement, SQL Server tente d'interdire les requêtes qui conduirait à l'infini les jeux d'enregistrements.

Vous pouvez lire cet article:

• SQL Server: sont l'expression de table commune récursive est vraiment à base de jeu?

Answer 2

L'algorithme que CTE utiliser est:

1. exécuter le point d'ancrage de la partie, obtenez le résultat r0

2. exécuter la partie récursive, à l'aide de r0 en tant qu'entrée et de sortie r1

3. exécuter la partie récursive, à l'aide de r1 en tant qu'entrée et de sortie r2

4. exécuter la partie récursive, à l'aide de r3 comme entrée et de sortie r3

   ...

5. exécuter la partie récursive, à l'aide de r(n-1) en entrée, et de sortie de la rn, une fois que rn est null, utilisez l'UNION de TOUS pour combiner r0, r1, r2 ... r(n-1) comme le résultat final

Prenons un exemple:

WITH    cte ( value )
          AS (
               SELECT   1
               UNION ALL
               SELECT   value + 1
               FROM     cte
               WHERE    value < 4
             )
    SELECT  *
    FROM    cte

Le résultat de cette requête est:

value
-----------
1
2
3
4

(4 row(s) affected)

Examinons étape par étape:

anchor result
     1            : r0
 as input to
     |
     |
     V
     2
 as input to      : r1
     |
     |
     V
     3
 as input to      : r2
     |
     |
     V
     4
 as input to      : r3
     |
     |
     V
   NULL


r0
UNION ALL
r1
UNION ALL       ------------>        1, 2, 3, 4
r2
UNION ALL
r3

Maintenant, nous utilisons l'UNION de TOUS pour combiner ces résultat(r0, r1, r2, r3) pour obtenir le résultat final.

Answer 3

Je pense qu'il se décompose comme ceci:

1. L'ancre instruction est exécutée. Cela vous donne un ensemble de résultats, appelé le jeu de base, ou de T0.

2. Le récursive instruction est exécutée, à l'aide de T0, la table pour exécuter la requête. Cela se produit automatiquement lorsque vous interrogez un CTE.

3. Si le membre récursif retourne des résultats, il crée une nouvelle série, T1. Le membre récursif est exécuté à nouveau, à l'aide de T1: entrée, la création d'T2 s'il y a des résultats.

4. Étape 3 se poursuit jusqu'à ce que plus les résultats sont générés, OU le nombre maximal de récurrences ont été fixés par le MAX_RECURSION option.

Cette page explique probablement les meilleurs. Il dispose d'une étape-par-étape de procédure pas à pas le chemin d'exécution d'un CTE.

Answer 4

Vous avez sans doute été de vouloir ce lien. Non, le point d'ancrage n'est pas exécutée plusieurs fois (il ne pouvait pas l'être, alors union all exigerait que tous les résultats s'affichent). Les détails au lien précédent.

Answer 5

Étape 1:

1 l'Europe NULL Europe
2 Asie NULL Asie

Étape 2:

1 l'Europe NULL Europe
2 Asie NULL Asie
3 Allemagne 1 Europe/Allemagne
4 royaume-UNI 1 Europe/royaume-UNI
5 Chine 2 Asie/Chine
6 L'Inde 2 De L'Asie/Inde

Étape 3:

1 l'Europe NULL Europe
2 Asie NULL Asie
3 Allemagne 1 Europe/Allemagne
4 royaume-UNI 1 Europe/royaume-UNI
5 Chine 2 Asie/Chine
6 L'Inde 2 De L'Asie/Inde
7 Ecosse 4 Europe/royaume-UNI/Ecosse

Étape 4:

1 l'Europe NULL Europe
2 Asie NULL Asie
3 Allemagne 1 Europe/Allemagne
4 royaume-UNI 1 Europe/royaume-UNI
5 Chine 2 Asie/Chine
6 L'Inde 2 De L'Asie/Inde
7 Ecosse 4 Europe/royaume-UNI/Ecosse
8 Edimbourg 7 l'Europe/royaume-UNI/Écosse/Edimbourg

Étape 5:

1 l'Europe NULL Europe 0
2 Asie NULL Asie 0
3 Allemagne 1 Europe/Allemagne 1
4 royaume-UNI 1 Europe/royaume-UNI 1
5 Chine 2 Asie/Chine 1
6 L'Inde 2 De L'Asie/Inde 1
7 Ecosse 4 Europe/royaume-UNI/Ecosse 2
8 Edimbourg 7 l'Europe/royaume-UNI/Écosse/Edimbourg 3
9 Leith 8 l'Europe/royaume-UNI/Écosse/Edimbourg/Leith 4

La dernière colonne à l'étape 5 est le Niveau. Au cours de chaque niveau, les lignes sont ajoutées à l'égard de ce qui est déjà disponible. Espérons que cette aide.