SQL树形结构表获取根节点及各根首个一级子节点的实现方案咨询

优化解法 #

你原有递归方案的问题在于会遍历整棵树的所有节点，包含不需要的深层子节点，针对需求仅需要根节点和一级子节点的场景，可以用非递归方案，性能更优，代码如下：

-- 通用标准SQL实现，兼容所有支持窗口函数的数据库
SELECT id, parent, label
FROM @disc
WHERE parent IS NULL

UNION ALL

SELECT id, parent, label
FROM (
    SELECT 
        id,
        parent,
        label,
        ROW_NUMBER() OVER(PARTITION BY parent ORDER BY id ASC) AS row_num
    FROM @disc
    WHERE parent IN (SELECT id FROM @disc WHERE parent IS NULL)
) first_level_child
WHERE row_num = 1
-- 按需调整排序规则，二选一即可
-- 排序1：根节点和对应子节点相邻输出（匹配第一种预期结果）
ORDER BY CASE WHEN parent IS NULL THEN id ELSE parent END, parent
-- 排序2：所有根节点在前，子节点在后（匹配第二种预期结果）
-- ORDER BY CASE WHEN parent IS NULL THEN 0 ELSE 1 END, id

可选更简洁写法（支持横向连接的数据库可用） #

如果使用SQL Server、PostgreSQL、MySQL 8.0及以上支持横向连接的数据库，可以用更简洁的写法，执行效率同样很高：

SELECT t.*
FROM @disc r
CROSS APPLY (
    -- 取根节点自身
    SELECT id, parent, label FROM @disc WHERE id = r.id
    UNION ALL
    -- 取该根节点下id最小的一级子节点
    SELECT TOP 1 id, parent, label FROM @disc WHERE parent = r.id ORDER BY id ASC
) t
WHERE r.parent IS NULL
-- 同样调整ORDER BY即可切换输出排序
ORDER BY CASE WHEN t.parent IS NULL THEN t.id ELSE t.parent END, t.parent

方案优势 #

• 无需递归遍历全树，直接过滤掉二级及以下的深层节点，数据量越大、树层级越深时性能优势越明显
• 逻辑直观易维护，窗口函数按父节点分组取组内id最小的第一条，完全匹配需求规则
• 仅需调整ORDER BY子句即可切换两种预期的输出排序格式

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