1
votes

Requête récursive CTE aux grands-parents

J'ai le tableau suivant:

;WITH ChildParent AS
(
    SELECT
        a.id,
        a.name,
        a.isinedit,
        a.parent_id,
        a.isdeleted
    FROM dbo.table 
    WHERE isdeleted = 0 AND isinedit = 0 

    UNION ALL

    SELECT
        a.id,
        a.name,
        a.isinedit,
        a.parent_id,
        a.isdeleted
    FROM dbo.table a
    INNER JOIN ChildParent cp ON a.parent_id = cp.id
    WHERE a.isdeleted = 0  AND a.isinedit = 0
)
SELECT  
        id,
        name,
    parent_id,
        isinedit,
        isdeleted
FROM ChildParent

Ce que je dois obtenir est une requête SELECT qui ne retournera que les lignes qui ont ISDELETED 0 et ISINEDIT 0 et dont les parents ou grands-parents sont également 0 p>

J'ai actuellement:

ID NAME PARENT_ID ISDELETED ISINEDIT
1  JJ     NULL     1          0
2  AR     1        0          0
3 PR      2        0          0
4 DR      NULL     0          1

Mais pour une raison quelconque, il renvoie des lignes doubles

sql sql-server subquery select recursive-query

1 commentaires

Je recommande de créer un VIEW qui n'affiche que les enregistrements valides, et de définir le CTE sur cette VIEW plutôt que sur la table de base. Vous pouvez également utiliser un CTE antérieur avec les mêmes critères de filtre btw.

3 Réponses :

1
votes

Vous devez ajouter le même prédicat isdeleted = 0 AND isinedit = 0 à la source INNER JOIN childParent CP .

... mais si vous faites cela vous faites votre requête CTE très fastidieuse et si vous devez répéter la même chose encore et encore, il y a probablement une meilleure façon de le faire.

... et il y en a! Une requête SELECT peut avoir plusieurs expressions CTE:

;
WITH filtered AS
(
    SELECT
        a.id,
        a.name,
        a.parent_id,
    FROM
        dbo.Table
    WHERE
        IsDeleted = 0
        AND
        IsInEdit = 0 
)

WITH cte AS
(
    SELECT
        a.id,
        a.name,
        a.parent_id
    FROM
        filtered

    UNION ALL

    SELECT
        a.id,
        a.name,
        a.parent_id
    FROM
        filtered
        INNER JOIN cte ON a.parent_id = cte.id
)
SELECT  
    *
FROM
    cte
ORDER BY
    id

0 commentaires

1
votes

Je pense que c'est un peu plus compliqué que vous ne le pensez. Si je comprends bien votre question, vous devez d'abord parcourir toute la hiérarchie des parents de chaque nœud, et ensuite vérifier si un parent ne respecte pas les règles (vous pouvez l'optimiser un peu en arrêtant après le premier parent non conforme est satisfait).

Vous devez également garder une trace du nœud d'origine, afin de pouvoir filtrer correctement dans la requête externe (ce qui évite les doublons que vous obtenez actuellement).

Je formulerais votre requête comme suit:

with cte as (
    select 
        t.*, 
        t.id original_id, 
        0 lvl, 
        1 is_ok
    from dbo.table t
    where isdeleted = 0 and isinedit = 0 
    union all
    select 
        t.*, 
        c.original_id, 
        c.lvl + 1, 
        case when t.isdeleted = 0 and t.isinedit = 0 then 1 else 0 end
    from dbo.table t
    inner join cte c on c.parent_id = t.id
    where c.is_ok = 1
)
select *
from cte c
where 
    c.lvl = 0
    and not exists (
        select 1 from cte c1 where c1.original_id = c.original_id and c1.is_ok = 0
    )

Notez que cette requête fonctionnera quel que soit le nombre de niveaux existant dans l'arborescence (si vous avez plus de 100 niveaux, vous devez ajouter option (maxrecursion 0) à la fin de la requête.

0 commentaires

0
votes

Ces types de requêtes sont plus faciles si vous créez d'abord un ensemble de données propre dans un CTE. Il supprime les clauses de filtrage supplémentaires dans toutes les requêtes CTE.

Dans ce cas, retourne toutes les lignes non supprimées et non dans is edit. La prochaine étape serait d'obtenir vos données. Grand-parent INNER JOIN Parent INNER JOIN CHILD

;WITH GoodDataRows AS
(
    SELECT
        a.id,
        a.name,
        a.isinedit,
        a.parent_id,
        a.isdeleted
    FROM dbo.table 
    WHERE isdeleted = 0 AND isinedit = 0 
)
SELECT
  *
FROM GoodDataRows gp
  INNER JOIN GoodDataRows p ON p.parent_id = gp.id
  INNER JOIN GoodDataRows c on c.parent_id = p.id

0 commentaires