如何在SQL中对含多级小数的clause列进行正确排序？

可以实现这种按数值逻辑排序的需求，核心思路是将层级索引的每一部分拆分出来，转换为数值类型后再排序，不同SQL方言的具体实现如下：

MySQL 实现 #

利用 SUBSTRING_INDEX 函数拆分每个层级的数字，转换为无符号整数后排序，即使层级数不固定也能适配：

SELECT clause
FROM your_table
ORDER BY
    CAST(SUBSTRING_INDEX(clause, '.', 1) AS UNSIGNED),
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(clause, '.', 2), '.', -1) AS UNSIGNED),
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(clause, '.', 3), '.', -1) AS UNSIGNED),
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(clause, '.', 4), '.', -1) AS UNSIGNED);

如果你的数据层级更多，可以继续扩展后续的排序条件。

PostgreSQL 实现 #

PostgreSQL 支持数组排序，直接将索引字符串转换为整数数组即可：

SELECT clause
FROM your_table
ORDER BY string_to_array(rtrim(clause, '.'), '.')::int[];

rtrim(clause, '.') 用于去除末尾的点，string_to_array 将字符串拆分为数组，再转换为整数类型，数组会按元素逐个数值比较排序。

SQL Server 实现 #

方法1：固定层级拆分 #

如果数据层级较少且固定，可通过字符串截取函数拆分各层级：

SELECT clause
FROM your_table
ORDER BY
    CAST(SUBSTRING(clause, 1, CHARINDEX('.', clause) - 1) AS INT),
    CASE 
        WHEN CHARINDEX('.', clause, CHARINDEX('.', clause) + 1) = 0 THEN 0
        ELSE CAST(SUBSTRING(clause, CHARINDEX('.', clause) + 1, CHARINDEX('.', clause, CHARINDEX('.', clause) + 1) - CHARINDEX('.', clause) - 1) AS INT)
    END,
    CASE 
        WHEN CHARINDEX('.', clause, CHARINDEX('.', clause, CHARINDEX('.', clause) + 1) + 1) = 0 THEN 0
        ELSE CAST(SUBSTRING(clause, CHARINDEX('.', clause, CHARINDEX('.', clause) + 1) + 1, CHARINDEX('.', clause, CHARINDEX('.', clause, CHARINDEX('.', clause) + 1) + 1) - CHARINDEX('.', clause, CHARINDEX('.', clause) + 1) - 1) AS INT)
    END;

方法2：动态层级处理（SQL Server 2016+） #

利用 STRING_SPLIT 拆分字符串，拼接补零后的层级字符串实现字典序适配数值序：

SELECT clause
FROM your_table
ORDER BY (
    SELECT CONCAT(REPLICATE('0', 10 - LEN(value)), value)
    FROM STRING_SPLIT(TRIM('.') FROM clause, '.')
    ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FOR XML PATH('')
);

这里给每个层级的数字补前导零到10位，拼接后的字符串字典序与数值逻辑一致。

优化方案：添加计算列 #

如果需要频繁执行这类排序，建议在表中添加持久化计算列，预先存储排序键：

ALTER TABLE your_table 
ADD clause_sort AS (
    SELECT CONCAT(REPLICATE('0', 10 - LEN(value)), value)
    FROM STRING_SPLIT(TRIM('.') FROM clause, '.')
    ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FOR XML PATH('')
) PERSISTED;

-- 查询时直接使用计算列排序
SELECT clause
FROM your_table
ORDER BY clause_sort;

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