如何对orders表中含数字字母的字符串字段table_number排序？

需求分析 #

现有orders表，table_number为字符串类型（数字与字母随机组合），需要按以下规则排序：

• 数字开头的记录排在字母开头的记录之前
• 数字开头的记录：先按开头的数字数值排序（如10排在6之后，而非字符串排序的10在2之前），数字相同则按后续字符排序（如1在前，1A在后）
• 字母开头的记录：按字符串自然排序（如A1 < B2 < B239 < C1...）

原始表结构与数据 #

目标排序结果 #

SQL实现示例 #

MySQL版本 #

利用正则表达式提取数字前缀，结合条件排序：

SELECT table_number, people_number
FROM orders
ORDER BY
    -- 标记是否为数字开头，数字开头的排前面
    CASE WHEN table_number REGEXP '^[0-9]' THEN 0 ELSE 1 END,
    -- 提取开头的数字部分转为数值排序
    CAST(REGEXP_SUBSTR(table_number, '^[0-9]+') AS UNSIGNED),
    -- 数字前缀后的剩余部分排序
    REGEXP_REPLACE(table_number, '^[0-9]+', ''),
    -- 字母开头的直接按原字符串排序
    table_number;

PostgreSQL版本 #

使用substring函数提取数字前缀：

SELECT table_number, people_number
FROM orders
ORDER BY
    CASE WHEN table_number ~ '^[0-9]' THEN 0 ELSE 1 END,
    -- 提取数字前缀并转为整数
    (substring(table_number from '^[0-9]+'))::integer,
    -- 剩余部分排序
    substring(table_number from '[^0-9].*'),
    table_number;

逻辑说明 #

1. 第一个排序条件：通过正则判断是否以数字开头，数字开头的标记为0，字母开头为1，确保数字开头的记录优先
2. 第二个排序条件：提取开头的连续数字转为数值类型，实现数字的自然排序（避免字符串排序时"10"<"2"的问题）
3. 第三个排序条件：去掉数字前缀后的剩余字符串，用于数字前缀相同的记录排序（如"1"和"1A"）
4. 最后一个排序条件：针对字母开头的记录，直接按原字符串排序，保证字母顺序正确

内容的提问来源于stack exchange，提问作者孙悟空