如何在单表中按父子层级顺序排序并展示查询结果？

我来帮你搞定这个父子关系的排序展示问题！你想要的是深度优先的树形遍历排序——也就是先展示一个根节点，然后把它的所有子节点（包括子子节点）依次列出来，再切换到下一个根节点继续这个逻辑。

你的原SQL之所以没达到预期，是因为coalesce(root,parent,id)只是按根节点分组，但排序时没有考虑节点的层级从属关系，而且category排序会打乱子节点跟随父节点的顺序（比如你示例里DESERT会因为字母顺序排在FRUIT后面，但你想要的是先展示DESERT和它的子节点，再处理FRUIT分支）。

通用解决方案（支持递归CTE的数据库：MySQL 8.0+/PostgreSQL/SQL Server等） #

用递归CTE生成每个节点的层级路径，然后按这个路径排序，就能完美实现你要的效果：

WITH RECURSIVE menu_hierarchy AS (
    -- 第一步：获取所有根节点（parent=0的节点），初始化排序路径为自身ID
    SELECT 
        id, 
        category, 
        parent, 
        root,
        CAST(id AS CHAR(255)) AS sort_path
    FROM tbl_menu
    WHERE parent = 0
    UNION ALL
    -- 第二步：递归遍历子节点，把父节点的路径和当前ID拼接成新的排序路径
    SELECT 
        m.id, 
        m.category, 
        m.parent, 
        m.root,
        CONCAT(mh.sort_path, ',', m.id) AS sort_path
    FROM tbl_menu m
    JOIN menu_hierarchy mh ON m.parent = mh.id
)
-- 最后按生成的排序路径输出结果
SELECT id, category, parent, root
FROM menu_hierarchy
ORDER BY sort_path;

脚本说明 #

• 递归CTE会先找出所有根节点（比如CLOTHES和FOOD & DRINK），然后逐层遍历它们的子节点，每个节点的sort_path会记录从根到当前节点的ID路径（比如SHORTS的路径是2,5,10，ICE CREAM的路径是1,4,9）。
• 按sort_path排序时，就会严格按照根→父→子的层级顺序排列，完全符合你想要的展示格式。

针对不支持递归CTE的老版本数据库（比如MySQL 5.x） #

如果你的数据库不支持递归CTE，且层级固定（最多3层），可以用拼接路径的方式临时解决：

SELECT 
    id, 
    category, 
    parent, 
    root
FROM tbl_menu
ORDER BY 
    CASE 
        -- 根节点用自身ID作为路径
        WHEN parent = 0 THEN CAST(id AS CHAR)
        -- 二级节点用根ID+自身ID
        WHEN root = parent THEN CONCAT(CAST(root AS CHAR), ',', CAST(id AS CHAR))
        -- 三级节点用根ID+父ID+自身ID
        ELSE CONCAT(CAST(root AS CHAR), ',', CAST(parent AS CHAR), ',', CAST(id AS CHAR))
    END;

不过这个方案只适用于固定层级的场景，如果后续有更深的层级，还是推荐用递归CTE的方案更灵活。

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