SQL Server 2016中带数字的VARCHAR列按段数值排序问题

我太懂这种烦恼了！SQL Server默认的字符串排序是按字符逐个比较的，所以才会出现1-1-1-20排在1-1-1-5前面的情况——毕竟字符'2'比'5'小嘛。要实现按-分隔的每一段数值来排序，我们得把字符串拆分成独立的数值段，再按这些段依次排序。

下面给你两种实用的解决方案，根据你的数据情况选就行：

方法一：用PARSENAME快速处理（适合段数固定的场景） #

如果你的数据最多只有4段（就像你例子里的那样），这个方法最简洁。我们先把-替换成.，然后用PARSENAME函数从右往左提取每一段，转成数值类型后排序：

SELECT YourColumnName
FROM YourTableName
ORDER BY
    -- 从最外层到最内层依次排序，NULLS FIRST保证段数少的排在前面
    CAST(PARSENAME(REPLACE(YourColumnName, '-', '.'), 4) AS DECIMAL(18,2)) NULLS FIRST,
    CAST(PARSENAME(REPLACE(YourColumnName, '-', '.'), 3) AS DECIMAL(18,2)) NULLS FIRST,
    CAST(PARSENAME(REPLACE(YourColumnName, '-', '.'), 2) AS DECIMAL(18,2)) NULLS FIRST,
    CAST(PARSENAME(REPLACE(YourColumnName, '-', '.'), 1) AS DECIMAL(18,2)) NULLS FIRST;

测试你的数据的话，1-15-2会被拆成1、15、2，而1-2拆成NULL、1、2，因为NULL优先排在前面，所以1-2会在1-15-2之前，完全符合你的期望结果。

方法二：递归CTE动态拆分（适合段数不固定的场景） #

如果你的数据段数不确定（可能超过4段），用递归CTE来动态拆分每一段更灵活：

WITH SplitCTE AS (
    -- 初始化：把字符串转成XML格式，方便拆分
    SELECT
        YourColumnName,
        CAST('<v>' + REPLACE(YourColumnName, '-', '</v><v>') + '</v>' AS XML) AS XmlValue,
        1 AS Level
    FROM YourTableName
    UNION ALL
    -- 递归拆分每一段，直到没有剩余部分
    SELECT
        YourColumnName,
        XmlValue.query('v[position()>1]'),
        Level + 1
    FROM SplitCTE
    WHERE XmlValue.exist('v[position()>1]') = 1
),
Segments AS (
    -- 提取每一段的数值和对应的层级
    SELECT
        YourColumnName,
        Level,
        CAST(XmlValue.value('v[1]', 'DECIMAL(18,2)') AS DECIMAL(18,2)) AS SegmentValue
    FROM SplitCTE
),
Pivoted AS (
    -- 把各层级的数值转成列，方便排序
    SELECT
        YourColumnName,
        MAX(CASE WHEN Level = 1 THEN SegmentValue END) AS Seg1,
        MAX(CASE WHEN Level = 2 THEN SegmentValue END) AS Seg2,
        MAX(CASE WHEN Level = 3 THEN SegmentValue END) AS Seg3,
        MAX(CASE WHEN Level = 4 THEN SegmentValue END) AS Seg4,
        MAX(CASE WHEN Level = 5 THEN SegmentValue END) AS Seg5 -- 可以根据实际情况增加更多层级
    FROM Segments
    GROUP BY YourColumnName
)
-- 按各层级数值排序
SELECT YourColumnName
FROM Pivoted
ORDER BY Seg1, Seg2, Seg3, Seg4, Seg5;

这个方法会自动处理任意段数的情况，不管你的数据是2段还是5段，都能正确按每段数值排序。

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