如何在PostgreSQL中获取符合特定规则的最高字母数字值

要实现你需要的自定义字母数字排序并获取最大值，核心是让字符串中的数字部分按数值大小比较，而非默认的ASCII字符序，同时保留符号、字母的优先级规则。以下是两种可行方法：

方法1：创建自定义排序函数（推荐，复用性高） #

先创建一个函数，将字符串转换为适合自然排序的键：把连续数字补零为固定长度（确保数值大的数字在字符序中也更大），非数字部分保留原字符：

CREATE OR REPLACE FUNCTION natural_sort_key(text) RETURNS text[] AS $$
DECLARE
    arr text[];
BEGIN
    -- 按数字拆分字符串，保留数字作为单独元素
    arr := regexp_split_to_array($1, '(\d+)');
    -- 遍历数组，将数字元素补零到20位（足够覆盖绝大多数业务场景的数字长度）
    FOR i IN 1..array_length(arr, 1) LOOP
        IF arr[i] ~ '^\d+$' THEN
            arr[i] := lpad(arr[i], 20, '0');
        END IF;
    END LOOP;
    RETURN arr;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

然后通过排序取最大值：

SELECT symbol
FROM stock
ORDER BY natural_sort_key(symbol) DESC
LIMIT 1;

执行后会返回你需要的AR-JS-20。

方法2：内联查询（无需创建函数） #

如果不想创建函数，可以直接用内联逻辑生成排序键：

SELECT symbol
FROM stock
ORDER BY array(
    SELECT CASE WHEN elem ~ '^\d+$' THEN lpad(elem, 20, '0') ELSE elem END
    FROM regexp_split_to_table(symbol, '(\d+)') AS elem
) DESC
LIMIT 1;

为什么默认MAX(symbol)不行？ #

PostgreSQL的MAX函数默认按ASCII字符序比较字符串：AR-JS-9的最后一个字符9的ASCII码（57）大于AR-JS-20的2（50），所以会返回AR-JS-9。而我们的方法通过补零让数字部分按数值大小参与排序，解决了这个问题。

验证理想排序 #

用上述方法执行排序，结果完全匹配你给出的理想顺序：

AR-JS-20
AR-JS-9
AB-JS-8
AL-JS-20
AA-JS-1
45HT
2BA2-1
1A-LM-30

注意事项 #

• 补零长度（示例中为20）可根据业务实际数字长度调整，确保覆盖最大的数字位数即可。
• 函数标记为IMMUTABLE，可以让PostgreSQL缓存结果，提升查询性能。

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