Код IT
← Каталог

Справочник по SQL — 7. CTE — Common Table Expressions

Фрагмент из «Справочник по SQL»: 7. CTE — Common Table Expressions.

sql sqlencyclopedia883 embed URL статья в энциклопедии
SQL main.sql 
WITH RECURSIVE tree AS (
    -- Anchor member (база рекурсии)
    SELECT id, parent_id, name, 0 AS level
    FROM nodes
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive member (шаг)
    SELECT n.id, n.parent_id, n.name, t.level + 1
    FROM nodes n
    INNER JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree ORDER BY level, name;
WITH RECURSIVE tree AS (
    -- Anchor member (база рекурсии)
    SELECT id, parent_id, name, 0 AS level
    FROM nodes
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive member (шаг)
    SELECT n.id, n.parent_id, n.name, t.level + 1
    FROM nodes n
    INNER JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree ORDER BY level, name;