SQL查询如何让自关联stages表按父级ID的连续层级顺序排序

问题原因 #

你原本的排序逻辑仅能处理一级父子关系，无法适配多级链式继承的排序需求：COALESCE(parentStageID, stageID)计算规则下，stageID为2的记录计算值为3，stageID为4的记录计算值为2，所以会出现4排在2前面的不符合预期的结果。

适用MySQL 8.0+/PostgreSQL/SQL Server等支持递归CTE的数据库方案 #

通过递归CTE生成节点的完整继承路径，按路径排序即可得到你要的连续父子继承顺序：

WITH RECURSIVE stage_hierarchy AS (
    -- 锚点查询：选中根节点
    SELECT 
        *,
        CAST(stageID AS CHAR(200)) AS sort_path
    FROM stages
    WHERE parentStageID IS NULL
    UNION ALL
    -- 递归查询：逐层匹配子节点，拼接继承路径
    SELECT 
        s.*,
        CONCAT(sh.sort_path, ',', s.stageID) AS sort_path
    FROM stages s
    INNER JOIN stage_hierarchy sh ON s.parentStageID = sh.stageID
)
SELECT stageID, stage, parentStageID 
FROM stage_hierarchy
ORDER BY sort_path;

运行后输出结果和你期望的完全一致：

老版本MySQL（5.x不支持递归CTE）可选方案 #

可以用自定义变量遍历生成排序序列：

SELECT s.*
FROM (
    SELECT 
        *,
        @path := IF(parentStageID IS NULL, stageID, CONCAT(@path, ',', stageID)) AS sort_path
    FROM stages
    ORDER BY COALESCE(parentStageID, stageID)
) s
ORDER BY sort_path;

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