MySQL中按record_number的年份与编号升序排序的实现方法

你需要基于record_number的固定格式（ent/[记录编号]/[创建年份]）实现自定义排序：升序时年份更早、记录编号更小的条目优先；降序时年份更新、记录编号更大的条目优先。

升序排序SQL #

SELECT id, record_number, created_at, updated_at
FROM your_table_name
ORDER BY
    -- 提取并转换年份为整数，按升序排列
    CAST(SUBSTRING_INDEX(record_number, '/', -1) AS UNSIGNED) ASC,
    -- 提取并转换中间的记录编号为整数，按升序排列
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(record_number, '/', 2), '/', -1) AS UNSIGNED) ASC;

降序排序SQL #

SELECT id, record_number, created_at, updated_at
FROM your_table_name
ORDER BY
    -- 年份按降序排列，更新的年份在前
    CAST(SUBSTRING_INDEX(record_number, '/', -1) AS UNSIGNED) DESC,
    -- 同年份下，记录编号按降序排列
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(record_number, '/', 2), '/', -1) AS UNSIGNED) DESC;

函数说明 #

• SUBSTRING_INDEX(record_number, '/', -1)：提取record_number最后一个/后的年份字符串，再通过CAST转为整数用于数值排序
• SUBSTRING_INDEX(SUBSTRING_INDEX(record_number, '/', 2), '/', -1)：先截取前两个/分隔的内容（如ent/4），再提取中间的记录编号字符串，转为整数后排序

降序结果验证 #

用你提供的示例数据执行降序查询，会得到如下符合需求的结果：

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