SQL Server中如何对多级版本号格式的字符串列排序？

针对多级版本号字符串（如1.01、1.01.01、1.）的排序需求，直接使用字符串排序会因字符比较规则导致结果不符合预期，可通过以下方法实现自然排序：

方法一：适配SQL Server 2022及以上版本 #

利用STRING_SPLIT的enable_ordinal参数保证拆分顺序，将每个版本段格式化为固定长度的数字字符串后拼接，再按拼接后的字符串排序：

SELECT Id
FROM VersionTable
ORDER BY 
    (SELECT STRING_AGG(FORMAT(CAST(value AS INT), 'D10'), '.') 
     FROM STRING_SPLIT(Id, '.', 1))

原理 #

• STRING_SPLIT(Id, '.', 1)：按.拆分版本号，1参数确保拆分后的段顺序与原字符串一致
• FORMAT(CAST(value AS INT), 'D10')：将每个版本段转为整数后格式化为10位字符串（不足补0），避免字符串比较时1.2排在1.01前面的问题
• STRING_AGG：将格式化后的段重新拼接，最终按拼接后的字符串排序即可实现自然排序

方法二：适配SQL Server 2016-2019版本 #

由于旧版本STRING_SPLIT不保证顺序，采用XML拆分法确保段顺序，再通过PIVOT将各段转为列后排序：

SELECT vt.Id
FROM VersionTable vt
CROSS APPLY (
    SELECT 
        CAST(Split.a.value('.', 'VARCHAR(100)') AS INT) AS VersionPart,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS PartIndex
    FROM (
        SELECT CAST('<M>' + REPLACE(vt.Id, '.', '</M><M>') + '</M>' AS XML) AS Data
    ) AS A
    CROSS APPLY Data.nodes('/M') AS Split(a)
) vs
PIVOT (
    MAX(VersionPart)
    FOR PartIndex IN ([1], [2], [3]) -- 按需扩展到更多版本级别，如[4], [5]等
) p
ORDER BY 
    ISNULL(p.[1], 0),
    ISNULL(p.[2], 0),
    ISNULL(p.[3], 0);

原理 #

• XML拆分法：将版本号转为XML节点，确保拆分后的各段顺序与原字符串一致
• PIVOT：将拆分后的行转列为多列（对应版本的各级别）
• ORDER BY：按各级别数字依次排序，空值用0填充，保证1.排在1.01之前

测试验证 #

针对给定的测试数据：

1.01
1.03.04
2.1
1.01.01
1.
2.
1.02

上述两种方法均可得到预期排序结果：

1.01
1.01.01
1.02
1.03.04
2.
2.1

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