SQL Server中如何按层级子字符串对多级编号数据正确排序

直接对带层级编号的字符串字段做字典序排序时，会逐字符比较ASCII值，导致2.10这类编号排在2.2之前，不符合层级排序预期。要实现正确的多级排序，核心是将.分隔的每一段数字单独转为数值类型逐段比较，支持任意层级的实现方式如下：

通用方案（支持任意未知层级，兼容SQL Server 2016及以上版本） #

通过递归CTE逐段拆分编号，将每段数字转为定长补0的字符串拼接为排序键，不管嵌套多少层都能正确排序：

WITH SplitCTE AS (
    SELECT 
        id,
        title,
        CAST(
            REPLICATE('0', 8 - LEN(TRY_CAST(LEFT(title, CHARINDEX('.', title)-1) AS INT))) 
            + TRY_CAST(LEFT(title, CHARINDEX('.', title)-1) AS VARCHAR(100))
        AS VARCHAR(MAX)) AS sortKey,
        STUFF(title, 1, CHARINDEX('.', title), '') AS remainStr
    FROM albums
    WHERE CHARINDEX('.', title) > 0

    UNION ALL

    SELECT 
        id,
        title,
        sortKey + '.' + REPLICATE('0', 8 - LEN(TRY_CAST(
            CASE WHEN CHARINDEX('.', remainStr) > 0 
                THEN LEFT(remainStr, CHARINDEX('.', remainStr)-1)
                ELSE LEFT(remainStr, CHARINDEX(' ', remainStr)-1)
            END AS INT))) 
            + TRY_CAST(
            CASE WHEN CHARINDEX('.', remainStr) > 0 
                THEN LEFT(remainStr, CHARINDEX('.', remainStr)-1)
                ELSE LEFT(remainStr, CHARINDEX(' ', remainStr)-1)
            END AS VARCHAR(100)),
        CASE WHEN CHARINDEX('.', remainStr) > 0 
            THEN STUFF(remainStr, 1, CHARINDEX('.', remainStr), '')
            ELSE '' END
    FROM SplitCTE
    WHERE LEN(remainStr) > 0 AND CHARINDEX(' ', remainStr) > 0
)
SELECT a.*
FROM albums a
LEFT JOIN (
    SELECT id, MAX(sortKey) AS sortKey FROM SplitCTE GROUP BY id
) s ON a.id = s.id
ORDER BY s.sortKey;

说明：代码中单段数字按8位长度补0，支持单段编号最大值为99999999，可根据业务实际编号长度调整补0位数。

简化方案（已知最大层级场景） #

如果业务中编号层级不超过4层（即编号中.数量不超过3个），可以直接用PARSENAME函数逐段提取编号，写法更简洁：

SELECT *
FROM albums
ORDER BY
    -- 提取最左侧第一层编号
    TRY_CAST(PARSENAME(LEFT(title, CHARINDEX(' ', title)-1), 4) AS INT),
    -- 第二层
    TRY_CAST(PARSENAME(LEFT(title, CHARINDEX(' ', title)-1), 3) AS INT),
    -- 第三层
    TRY_CAST(PARSENAME(LEFT(title, CHARINDEX(' ', title)-1), 2) AS INT),
    -- 第四层
    TRY_CAST(PARSENAME(LEFT(title, CHARINDEX(' ', title)-1), 1) AS INT);

注意：PARSENAME原本用于解析SQL Server四段式对象名，因此最多支持4段编号，超过4层的场景请使用通用递归方案。

排序效果 #

执行查询后结果会按层级数字逻辑正确排序：

title:
1. Album one
2. Album two
2.1 - Song one
2.2 - Song two
2.10 - Song ten
2.2.1.1 - Track 1
2.2.1.2 - Track 2
2.2.2 - Track 3
2.2.3 - Track 4

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