如何根据另一列的关联值对SQL查询结果实现层级排序？

你这个需求本质是树形结构的顺序遍历：Col B存储的是当前Col A节点的父节点ID，根节点是Col B为null的记录，要求所有子节点必须排在对应父节点的后方。

主流关系型数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11gR2+）都可以用**递归CTE（公共表表达式）**实现，逻辑通用、可维护性高。

实现步骤 #

• 先定位根节点（Col B为null的顶层记录），作为递归的起始点
• 递归关联下一层子节点（即Col B等于上一层Col A的记录），同时记录每个节点的层级、从根到当前节点的完整排序路径
• 最终查询时按拼接好的完整路径排序，自然就能保证父节点永远排在子节点前面

代码示例 #

假设你的表名为node_relation，可直接执行以下语句：

WITH RECURSIVE hierarchy_cte AS (
    -- 锚点：查询根节点，初始化层级和排序路径
    SELECT 
        `Col A`,
        `Col B`,
        1 AS node_level,
        CAST(`Col A` AS CHAR(500)) AS sort_path
    FROM node_relation
    WHERE `Col B` IS NULL

    UNION ALL

    -- 递归：逐层关联子节点，拼接路径
    SELECT 
        t.`Col A`,
        t.`Col B`,
        h.node_level + 1 AS node_level,
        CONCAT(h.sort_path, '>', t.`Col A`) AS sort_path
    FROM node_relation t
    JOIN hierarchy_cte h ON t.`Col B` = h.`Col A`
)
-- 按路径排序输出结果
SELECT `Col A`
FROM hierarchy_cte
ORDER BY sort_path;

结果说明 #

执行后每个节点生成的排序路径如下：

• 000000：000000
• 000758：000000>000758
• 000244：000000>000244
• 000924：000000>000244>000924

按字符串规则排序这些路径，输出结果和预期完全一致。

注意事项 #

• 同层级节点如果需要自定义顺序，可以在路径拼接时增加排序字段，比如要让同层级的000244排在000758前面，调整递归部分的关联排序逻辑即可
• 如果使用不支持递归CTE的老旧数据库（比如MySQL 5.x），需要自定义函数计算每个节点的全路径再排序，性能较差，优先建议升级数据库版本
• 使用前需要校验数据中不存在循环引用（比如A的父是B，B的父是A），否则会触发递归深度超限的报错，部分数据库支持设置最大递归深度来规避异常

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