使用CTE查询时基于拼接Display Order的VarChar类型层级排序问题

这个问题我之前处理过好多次——用连字符拼接的层级display order字符串排序确实会踩字典序的坑，比如2-0-0会排在10-0前面，完全不符合我们要的层级数字排序逻辑。核心原因是字符串排序是按单个字符逐一比较，而我们需要的是按每个层级的数字大小依次对比。下面给你几种不同数据库下的实用解决方案，按需选用：

方案1：拆分层级为独立整数列（最稳妥，性能最优） #

如果你的层级深度是固定的，或者能预估最大层级数，最推荐这种方式：在CTE里把拼接的字符串拆成每个层级单独的整数列，然后按这些列排序。比如假设最多支持4级：

WITH HierarchyCTE AS (
    -- 这里替换成你的递归CTE逻辑，生成包含FullDisplayOrder的结果集
    SELECT 
        ID,
        NodeName,
        FullDisplayOrder,
        -- 拆分每个层级为整数
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(FullDisplayOrder, '-', 1), '-', -1) AS UNSIGNED) AS Level1,
        CAST(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(FullDisplayOrder, '-', 2), '-', -1), 0) AS UNSIGNED) AS Level2,
        CAST(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(FullDisplayOrder, '-', 3), '-', -1), 0) AS UNSIGNED) AS Level3,
        CAST(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(FullDisplayOrder, '-', 4), '-', -1), 0) AS UNSIGNED) AS Level4
    FROM YourBaseTable
    -- 递归关联逻辑...
)
SELECT ID, NodeName, FullDisplayOrder
FROM HierarchyCTE
ORDER BY Level1, Level2, Level3, Level4;

这种方式排序精准，而且整数排序的性能比字符串处理好很多，适合数据量较大的场景。

方案2：填充层级为固定长度（适配可变深度） #

如果你的层级深度不固定，比如有的节点是2级，有的是5级，可以把每个数字段填充成相同长度的字符串（比如前面补0），这样字典序排序就和数字排序一致了。

MySQL 8.0+ 示例 #

WITH HierarchyCTE AS (
    SELECT 
        ID,
        NodeName,
        FullDisplayOrder,
        -- 把每个数字段补0到3位，再重新拼接成排序用的字符串
        GROUP_CONCAT(LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(FullDisplayOrder, '-', n), '-', -1), 3, '0') ORDER BY n SEPARATOR '-') AS SortedOrder
    FROM YourHierarchyTable
    -- 生成足够多的数字序列覆盖最大层级（这里生成1-5，可按需调整）
    JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS NumSequence
        ON n <= LENGTH(FullDisplayOrder) - LENGTH(REPLACE(FullDisplayOrder, '-', '')) + 1
    GROUP BY ID, NodeName, FullDisplayOrder
)
SELECT ID, NodeName, FullDisplayOrder
FROM HierarchyCTE
ORDER BY SortedOrder;

SQL Server 示例 #

WITH HierarchyCTE AS (
    SELECT 
        ID,
        NodeName,
        FullDisplayOrder,
        -- 拆分后补0到3位，再按原顺序拼接
        STRING_AGG(FORMAT(CAST(value AS INT), 'D3'), '-') WITHIN GROUP (ORDER BY CHARINDEX('-' + value + '-', '-' + FullDisplayOrder + '-')) AS SortedOrder
    FROM YourHierarchyTable
    CROSS APPLY STRING_SPLIT(FullDisplayOrder, '-')
    GROUP BY ID, NodeName, FullDisplayOrder
)
SELECT ID, NodeName, FullDisplayOrder
FROM HierarchyCTE
ORDER BY SortedOrder;

方案3：直接在ORDER BY中动态处理（无需新增列） #

如果不想在CTE里新增列，也可以直接在ORDER BY子句中拆分每个层级进行比较：

PostgreSQL 专属简化方案 #

PostgreSQL支持直接把字符串转成整数数组，数组排序会自动按每个元素依次比较，完美适配需求：

SELECT ID, NodeName, FullDisplayOrder
FROM YourHierarchyTable
ORDER BY string_to_array(FullDisplayOrder, '-')::int[];

MySQL 8.0+ 直接排序示例 #

SELECT ID, NodeName, FullDisplayOrder
FROM YourHierarchyTable
ORDER BY 
    CAST(SUBSTRING_INDEX(FullDisplayOrder, '-', 1) AS UNSIGNED),
    CAST(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(FullDisplayOrder, '-', 2), '-', -1), 0) AS UNSIGNED),
    CAST(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(FullDisplayOrder, '-', 3), '-', -1), 0) AS UNSIGNED),
    CAST(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(FullDisplayOrder, '-', 4), '-', -1), 0) AS UNSIGNED);

这里需要列出足够多的层级，确保覆盖你的数据里的最大深度。

核心思路回顾 #

不管用哪种方案，本质都是绕开字符串的字典序排序，把每个层级的字符串数字转换成可按数值大小比较的类型（整数或固定长度字符串），然后按层级顺序依次排序。根据你的数据库类型、层级是否固定以及数据量大小，选择最适合的方式就好。

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