如何在SQL中先按itemType排序再实现父子层级排序

嘿，这个需求我熟！要实现先按itemType排序顶级节点，再保持父子层级关联的效果，用递归CTE（公共表表达式）就能完美解决，具体方案如下：

核心思路 #

1. 先筛选出所有顶级节点（parentItemId为0或null的记录），给它们生成包含itemType的排序路径，确保顶级节点按itemType升序排列；
2. 通过递归关联子节点，继承父节点的排序路径，保证子节点始终紧跟对应的父节点；
3. 最后按生成的排序路径整体排序，就能得到符合要求的层级结构。

完整SQL代码 #

WITH Hierarchy AS (
    -- 锚点成员：获取所有顶级节点
    SELECT 
        AutoId,
        itemId,
        parentItemId,
        itemType,
        -- 生成排序路径：先按itemType，再按itemId确保顶级节点排序正确
        CAST(itemType AS VARCHAR(MAX)) + '.' + CAST(itemId AS VARCHAR(MAX)) AS SortPath,
        1 AS Level  -- 记录当前节点层级
    FROM YourTableName  -- 替换成你的实际表名
    WHERE parentItemId IS NULL OR parentItemId = 0

    UNION ALL

    -- 递归成员：关联子节点，继承父节点的排序路径
    SELECT 
        child.AutoId,
        child.itemId,
        child.parentItemId,
        child.itemType,
        parent.SortPath + '.' + CAST(child.itemId AS VARCHAR(MAX)) AS SortPath,
        parent.Level + 1 AS Level
    FROM YourTableName child
    INNER JOIN Hierarchy parent ON child.parentItemId = parent.itemId
)

-- 最终查询：按排序路径输出结果
SELECT 
    AutoId,
    itemId,
    parentItemId,
    itemType
FROM Hierarchy
ORDER BY SortPath;

效果验证 #

用你提供的示例数据测试，输出结果会和你的期望一致：

• 顶级节点按itemType升序排列（1→2→3→4→5→6）；
• 每个顶级节点的子节点会紧跟在父节点之后，保持父子层级关系。

扩展说明 #

如果后续子节点的itemType也需要参与排序，只需修改递归部分的SortPath生成逻辑，把子节点的itemType也加入路径即可：

parent.SortPath + '.' + CAST(child.itemType AS VARCHAR(MAX)) + '.' + CAST(child.itemId AS VARCHAR(MAX)) AS SortPath

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