如何从含多根节点与child_order列的单表中检索父子树？

表结构 #

CREATE TABLE tbl 
(
    id int,
    parent_id int,
    child_order int,
    name varchar(20)
)

样本数据集 #

1   NULL    0   BtVS
2   NULL    1   Star Trek
3   1       0   Buffy
4   1       1   Willow
5   1       2   Xander
6   1       3   Giles
7   2       0   Kirk
8   2       1   Spock
9   2       2   McCoy
10  2       3   Uhura
11  3       0   Strength
12  3       1   Speed
13  3       2   Dreams
14  4       0   Tech
15  4       1   Magic
16  4       2   Dreams
17  5       0   Heart
18  5       1   Humor
19  5       2   Loyalty
20  6       0   Wisdom
21  6       1   Training
22  6       2   Guidance
23  7       0   Leadership
24  7       1   Independence
25  7       2   Courage
26  8       0   Logic
27  8       1   Strength
28  8       2   Nerve Pinch
29  9       0   Healing
30  9       1   Sarcasm
31  9       2   Friendship
32  10      0   Communications
33  10      1   Languages
34  10      2   Music

期望输出 #

1   1   BtVS
3   2       Buffy
11  3           Strength
12  3           Speed
13  3           Dreams
4   2       Willow
14  3           Tech
15  3           Magic
16  3           Dreams
5   2       Xander
17  3           Heart
18  3           Humor
19  3           Loyalty
6   2       Giles
20  3           Wisdom
21  3           Training
22  3           Guidance
2   1   Star Trek
7   2       Kirk
23  3           Leadership
24  3           Independence
25  3           Courage
8   2       Spock
26  3           Logic
27  3           Strength
28  3           Nerve Pinch
9   2       McCoy
29  3           Healing
30  3           Sarcasm
31  3           Friendship
10  2       Uhura
32  3           Communications
33  3           Languages
34  3           Music

需求说明 #

输出需包含id列、层级（report level）和name列，并按指定顺序排列：

• 根节点按child_order排序
• 每个父节点下的子节点按child_order排序
• child_order可能不连续，仅相对顺序有效，子节点数量可能超过10个

遇到的问题 #

尝试用CTE实现，但无法处理多根节点，且CTE内无法直接用ORDER BY适配child_order排序；曾考虑用10的幂次生成排序值，但两位数的child_order会导致排序错误，求合理实现方法。

可以用递归CTE生成排序键和层级，通过固定长度的字符串拼接解决child_order的排序问题，避免10幂次的缺陷。具体思路：

1. 锚点查询处理所有根节点（parent_id IS NULL），生成初始固定长度排序键，标记层级为1。
2. 递归查询拼接父节点排序键与当前节点的固定长度child_order字符串，作为当前节点排序键，层级在父节点基础上加1。
3. 最后按排序键排序，同时根据层级生成缩进格式的name。

以下是完整SQL代码（以MySQL为例，其他数据库仅需调整字符串补零函数：SQL Server用RIGHT('00000' + CAST(child_order AS VARCHAR),5)，PostgreSQL用LPAD(child_order::TEXT,5,'0')）：

WITH RECURSIVE cte AS (
    -- 锚点：根节点
    SELECT 
        id,
        parent_id,
        child_order,
        name,
        1 AS report_level,
        -- 生成5位固定长度排序键，足够应对大量子节点
        LPAD(child_order, 5, '0') AS sort_key
    FROM tbl
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归：子节点
    SELECT 
        t.id,
        t.parent_id,
        t.child_order,
        t.name,
        cte.report_level + 1 AS report_level,
        -- 拼接父节点排序键与当前节点固定长度child_order
        CONCAT(cte.sort_key, LPAD(t.child_order, 5, '0')) AS sort_key
    FROM tbl t
    JOIN cte ON t.parent_id = cte.id
)
SELECT 
    id,
    report_level,
    -- 按层级生成缩进，每个层级加4个空格（匹配期望输出格式）
    CONCAT(REPEAT('    ', report_level - 1), name) AS name
FROM cte
ORDER BY sort_key;

代码说明 #

• 排序键生成：用LPAD把child_order补成固定长度字符串，比如5位，这样0、9、10会变成00000、00009、00010，拼接后排序能保证正确顺序，不会出现10排在1前面的问题。
• 多根节点处理：锚点查询直接筛选所有parent_id IS NULL的记录，自然支持多根节点，根节点排序由自身child_order决定。
• 层级与缩进：通过递归计算report_level，用REPEAT生成对应数量的空格实现缩进，和期望输出格式完全匹配。

执行后会得到与需求一致的输出结果。

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