SQL按path升序排序结果与Windows文件夹排序不一致的问题

SQL默认采用字典序（逐字符ASCII值比较）排序，而Windows文件夹使用自然排序——会将字符串中的数字段识别为数值，按数值大小排序，这是两者结果差异的核心原因。比如路径2025\2009\10，字典序会把它排在2025\2009\2前面，但Windows会按数值比较，让2排在10前面。

以下是不同数据库下的具体实现方案：

SQL Server #

通过自定义函数生成自然排序键，实现Windows风格排序：

1. 创建排序键生成函数：

CREATE FUNCTION dbo.AlphaNumericSortKey(@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @output NVARCHAR(MAX) = ''
    DECLARE @currentChar CHAR(1), @isDigit BIT = 0, @currentNumber NVARCHAR(20) = ''
    DECLARE @i INT = 1

    WHILE @i <= LEN(@input)
    BEGIN
        SET @currentChar = SUBSTRING(@input, @i, 1)
        IF @currentChar BETWEEN '0' AND '9'
        BEGIN
            SET @currentNumber += @currentChar
            SET @isDigit = 1
        END
        ELSE
        BEGIN
            IF @isDigit = 1
            BEGIN
                SET @output += RIGHT('0000000000' + @currentNumber, 10)
                SET @currentNumber = ''
                SET @isDigit = 0
            END
            SET @output += @currentChar
        END
        SET @i += 1
    END
    IF @isDigit = 1
        SET @output += RIGHT('0000000000' + @currentNumber, 10)
    RETURN @output
END

2. 修改查询语句：

SELECT *
FROM [folder_levl]
WHERE path LIKE '2025\2009%'
ORDER BY dbo.AlphaNumericSortKey(path) ASC

MySQL/MariaDB #

利用正则表达式拆分路径中的非数字和数字部分，分别排序：

SELECT *
FROM [folder_levl]
WHERE path LIKE '2025\\2009%'
ORDER BY 
    REGEXP_REPLACE(path, '[0-9]+', '') ASC,
    CAST(REGEXP_REPLACE(path, '[^0-9]+', '') AS UNSIGNED) ASC

注：多层数字路径场景下，需调整正则逻辑实现递归拆分。

PostgreSQL #

通过拆分路径片段，将数字段补前导零后排序：

SELECT *
FROM folder_levl
WHERE path LIKE '2025\2009%'
ORDER BY (
    SELECT array_agg(
        CASE WHEN substring(part from '^\d+$') IS NOT NULL 
             THEN lpad(part, 20, '0') 
             ELSE part 
        END
    )
    FROM unnest(regexp_split_to_array(path, '(\d+)')) AS part
)

性能优化提示 #

自定义排序逻辑可能降低大数据量查询性能，建议基于排序键创建计算列并添加索引，提升排序效率。

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