实用SQL CTE查询：实现带排序与子树聚合的深度优先树形结构

前提准备 #

假设我们有一个CMS分类表 categories，结构如下：

CREATE TABLE categories (
    id INT PRIMARY KEY,
    parent_id INT NULL REFERENCES categories(id),
    title VARCHAR(100) NOT NULL,
    -- 假设每个分类下直接关联的已发布文章数，实际场景可通过关联articles表计算
    published_articles INT DEFAULT 0
);

1. 深度优先+同层级按title排序的树形列表 #

用递归CTE生成深度优先结构，同时维护排序路径字段，确保同层级按title排序，整体保持深度优先：

MySQL 8+ 写法 #

WITH RECURSIVE category_tree AS (
    -- 锚点：根节点（parent_id IS NULL）
    SELECT
        id,
        parent_id,
        title,
        published_articles,
        1 AS depth,
        -- 生成排序路径：根节点按title排序，子节点拼接父节点路径+自身标识
        CONCAT(LPAD(id, 10, '0'), '-', title) AS sort_path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归：关联子节点
    SELECT
        c.id,
        c.parent_id,
        c.title,
        c.published_articles,
        ct.depth + 1 AS depth,
        CONCAT(ct.sort_path, '|', LPAD(c.id, 10, '0'), '-', c.title) AS sort_path
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
-- 按排序路径实现深度优先+同层级title排序
SELECT id, parent_id, title, depth, published_articles
FROM category_tree
ORDER BY sort_path;

PostgreSQL 写法 #

用数组维护排序键，更简洁直观：

WITH RECURSIVE category_tree AS (
    SELECT
        id,
        parent_id,
        title,
        published_articles,
        1 AS depth,
        ARRAY[title, id::TEXT] AS sort_key
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT
        c.id,
        c.parent_id,
        c.title,
        c.published_articles,
        ct.depth + 1,
        ct.sort_key || ARRAY[c.title, c.id::TEXT]
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, parent_id, title, depth, published_articles
FROM category_tree
ORDER BY sort_key;

说明：用LPAD(id,10,'0')是为了避免数字id排序时出现"10"排在"2"前面的问题，确保数值顺序的正确性。

2. 子树聚合（将子节点数据汇总到祖先） #

实现把所有子节点的published_articles汇总到每个祖先节点，同时过滤出至少有一个已发布文章的分类：

通用写法（适配多数支持CTE的数据库） #

WITH RECURSIVE category_hierarchy AS (
    -- 锚点：所有节点作为起始，后续递归获取其所有后代
    SELECT
        id AS ancestor_id,
        id AS descendant_id,
        published_articles
    FROM categories
    UNION ALL
    SELECT
        ch.ancestor_id,
        c.id AS descendant_id,
        c.published_articles
    FROM category_hierarchy ch
    JOIN categories c ON ch.descendant_id = c.parent_id
),
-- 聚合每个祖先的子树总发布数
category_agg AS (
    SELECT
        ancestor_id,
        SUM(published_articles) AS total_published
    FROM category_hierarchy
    GROUP BY ancestor_id
)
-- 关联原表，过滤有效分类并保留树形排序
SELECT
    c.id,
    c.parent_id,
    c.title,
    ca.total_published
FROM categories c
JOIN category_agg ca ON c.id = ca.ancestor_id
WHERE ca.total_published > 0
ORDER BY (
    -- 复用深度优先排序路径逻辑
    WITH RECURSIVE sort_path AS (
        SELECT id, CONCAT(LPAD(id,10,'0'), '-', title) AS path
        FROM categories WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id, CONCAT(sp.path, '|', LPAD(c.id,10,'0'), '-', c.title)
        FROM categories c JOIN sort_path sp ON c.parent_id = sp.id
    )
    SELECT path FROM sort_path WHERE id = c.id
);

优化写法（反向递归，从子节点向上聚合） #

从叶子节点往上递归，直接计算每个节点的总发布数，性能更优：

WITH RECURSIVE category_agg AS (
    -- 锚点：叶子节点（无下属子节点）
    SELECT
        id,
        parent_id,
        title,
        published_articles AS total_published
    FROM categories c
    WHERE NOT EXISTS (SELECT 1 FROM categories WHERE parent_id = c.id)
    UNION ALL
    -- 递归：父节点汇总自身+所有子节点的总发布数
    SELECT
        c.id,
        c.parent_id,
        c.title,
        c.published_articles + SUM(ca.total_published) AS total_published
    FROM categories c
    JOIN category_agg ca ON c.id = ca.parent_id
    GROUP BY c.id, c.parent_id, c.title, c.published_articles
    -- 补充根节点（若根节点不是叶子节点）
    UNION ALL
    SELECT id, parent_id, title, published_articles
    FROM categories
    WHERE parent_id IS NULL AND NOT EXISTS (SELECT 1 FROM category_agg WHERE id = categories.id)
)
SELECT id, parent_id, title, total_published
FROM category_agg
WHERE total_published > 0
ORDER BY (
    -- 复用深度优先排序路径逻辑
    WITH RECURSIVE sort_path AS (
        SELECT id, CONCAT(LPAD(id,10,'0'), '-', title) AS path
        FROM categories WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id, CONCAT(sp.path, '|', LPAD(c.id,10,'0'), '-', c.title)
        FROM categories c JOIN sort_path sp ON c.parent_id = sp.id
    )
    SELECT path FROM sort_path WHERE id = category_agg.id
);

关键注意事项 #

• 排序路径稳定性：必须确保排序路径能准确反映深度优先层级关系，同时同层级按指定字段排序，避免排序混乱。
• 性能优化：大数据量表需给parent_id建立索引，递归CTE的性能依赖索引有效性。
• 数据库兼容性：不同数据库的字符串拼接、数组处理语法有差异，比如MySQL用CONCAT，PostgreSQL用||，需根据实际数据库调整。

内容的提问来源于stack exchange，提问作者ducktype