Comment obtenir les détails de l'ancre en utilisant le CTE ?

Question

J'ai un tableau avec 2 colonnes (id, childId) avec les données suivantes :

• 1, 2
• 2, 4
• 3, 5
• 4, 6
• 5, null
• 6, null

J'ai le CTE suivant qui récupère les enregistrements et cela fonctionne bien.

DECLARE @id TABLE (id int, childId int);
INSERT INTO @id SELECT 1, 2;
INSERT INTO @id SELECT 2, 4;
INSERT INTO @id SELECT 3, 5;
INSERT INTO @id SELECT 4, 6;
INSERT INTO @id SELECT 5, null;
INSERT INTO @id SELECT 6, null;

WITH cte AS  
(
    SELECT id, childId
    FROM @id
    WHERE id = 1
    UNION ALL
    SELECT b.id, b.childId
    FROM @id b
    INNER JOIN cte 
        ON b.id = cte.childId
)
SELECT * FROM cte

Cependant, j'aimerais ajouter les détails de l'ancre pour que les résultats ressemblent à ceci :

• 1, 2, null
• 2, 4, 1
• 4, 6, 1
• 6, null, 1

Ainsi, la troisième colonne est l'enregistrement d'ancrage principal.

Est-ce possible ?

Answer 1

WITH cte AS  
(
    SELECT id, childId, id AS anchorId
    FROM mytable
    WHERE 
        id IN (SELECT id FROM @id)
    UNION ALL
    SELECT b.id, b.childId, cte.anchorId
    FROM mytable b
    INNER JOIN cte 
        ON b.id = cte.childId
)
SELECT
    id
    , childId
    , case
        WHEN id = anchorId THEN NULL
        ELSE anchorId
    END as anchorId
FROM cte

---

Avec code de test fourni :

DECLARE @id TABLE (id int, childId int);
INSERT INTO @id SELECT 1, 2;
INSERT INTO @id SELECT 2, 4;
INSERT INTO @id SELECT 3, 5;
INSERT INTO @id SELECT 4, 6;
INSERT INTO @id SELECT 5, null;
INSERT INTO @id SELECT 6, null;

WITH cte AS  
(
    SELECT id, childId, id AS anchorId
    FROM @id
    WHERE id IN (1,3)
    UNION ALL
    SELECT b.id, b.childId, cte.anchorId
    FROM @id b
    INNER JOIN cte 
        ON b.id = cte.childId
)
SELECT
    id
    , childId
    , CASE
        WHEN id = anchorId THEN NULL
        ELSE anchorId
    END AS anchorId
FROM cte

Answer 2

Il suffit d'ajouter une troisième colonne dans le CTE.

declare @mytable table ( id int, childId int );
insert  @mytable
        ( id, childId )
values  ( 1, 2 ),
        ( 2, 4 ),
        ( 3, 5 ),
        ( 4, 6 ),
        ( 5, null ),
        ( 6, null );
declare @id table ( id int );
insert  into @id
        select  1;
insert  into @id
        select  3;

with    cte
          as ( select   id ,
                        childId ,
                        id root
               from     @mytable
               where    id in ( select  id
                                from    @id )
               union all
               select   b.id ,
                        b.childId ,
                        cte.root
               from     @mytable b
                        inner join cte on b.id = cte.childId
             )
    select  *
    from    cte;