如何在MySQL/PostgreSQL中按日期排序varchar类型执行编号

我有一张名为exedetails的表，其中exe_number列类型为varchar(100)，存储格式为DDMon_E*（例如02Aug_E1，DD为日期、Mon为月份缩写、E*为执行迭代标识）。

表数据及结构 #

查询表数据 #

select * from exedetails;
+------------+------------+------------+-------------+
| exe_number | pass_count | fail_count | error_count |
+------------+------------+------------+-------------+
| 02Aug_E1   |         98 |          9 |           0 |
| 31Jul_E1   |         94 |          8 |           1 |
| 30Jul_E2   |         76 |          9 |           3 |
| 01Aug_E2   |         98 |          7 |           0 |
| 02Aug_E2   |         76 |          8 |           2 |
| 30Jul_E1   |         98 |         12 |           9 |
| 31Jul_E2   |         91 |          6 |           1 |
| 01Aug_E1   |         67 |         14 |           2 |
+------------+------------+------------+-------------+
8 rows in set (0.00 sec)

表结构 #

describe exedetails;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| exe_number  | varchar(100) | YES  |     | NULL    |       |
| pass_count  | int          | YES  |     | NULL    |       |
| fail_count  | int          | YES  |     | NULL    |       |
| error_count | int          | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

当前日期为02 August 2022，我希望查询结果按日期倒序排列（当日02Aug_开头的记录排在最顶部），但直接按exe_number排序无法得到预期结果：

错误的排序结果 #

按exe_number降序

select exe_number from exedetails order by exe_number desc;
+------------+
| exe_number |
+------------+
| 31Jul_E2   |
| 31Jul_E1   |
| 30Jul_E2   |
| 30Jul_E1   |
| 02Aug_E2   |
| 02Aug_E1   |
| 01Aug_E2   |
| 01Aug_E1   |
+------------+
8 rows in set (0.00 sec)

按exe_number升序

select exe_number from exedetails order by exe_number asc;
+------------+
| exe_number |
+------------+
| 01Aug_E1   |
| 01Aug_E2   |
| 02Aug_E1   |
| 02Aug_E2   |
| 30Jul_E1   |
| 30Jul_E2   |
| 31Jul_E1   |
| 31Jul_E2   |
+------------+
8 rows in set (0.00 sec)

预期结果 #

+------------+
| exe_number |
+------------+
| 02Aug_E1   |
| 02Aug_E2   |
| 01Aug_E1   |
| 01Aug_E2   |
| 31Jul_E1   |
| 31Jul_E2   |
| 30Jul_E1   |
| 30Jul_E2   |
+------------+

请问在MySQL/PostgreSQL中如何实现该需求？

核心思路是从exe_number中提取日期部分转换为日期类型后按倒序排序，同时对同一日期内的记录按迭代标识（E*中的数字）升序排序。

MySQL 实现 #

基础实现（按日期倒序+迭代号升序） #

利用SUBSTRING提取exe_number的前5位（DDMon），结合STR_TO_DATE转换为日期，再按日期倒序、迭代号升序排列：

SELECT exe_number
FROM exedetails
ORDER BY
    STR_TO_DATE(SUBSTRING(exe_number, 1, 5), '%d%b') DESC,
    CAST(SUBSTRING_INDEX(exe_number, 'E', -1) AS UNSIGNED) ASC;

强制置顶当日记录（可选） #

如果需要明确将当日（02Aug）的记录排在最前面，可增加优先级排序条件：

SELECT exe_number
FROM exedetails
ORDER BY
    CASE WHEN SUBSTRING(exe_number, 1, 5) = '02Aug' THEN 0 ELSE 1 END,
    STR_TO_DATE(SUBSTRING(exe_number, 1, 5), '%d%b') DESC,
    CAST(SUBSTRING_INDEX(exe_number, 'E', -1) AS UNSIGNED) ASC;

PostgreSQL 实现 #

基础实现（按日期倒序+迭代号升序） #

利用LEFT提取前5位，结合TO_DATE转换为日期，再按日期倒序、迭代号升序排列：

SELECT exe_number
FROM exedetails
ORDER BY
    TO_DATE(LEFT(exe_number, 5), 'DDMon') DESC,
    CAST(SUBSTRING(exe_number FROM 'E(\d+)') AS INTEGER) ASC;

强制置顶当日记录（可选） #

同样可以强制当日记录排最前：

SELECT exe_number
FROM exedetails
ORDER BY
    CASE WHEN LEFT(exe_number, 5) = '02Aug' THEN 0 ELSE 1 END,
    TO_DATE(LEFT(exe_number, 5), 'DDMon') DESC,
    CAST(SUBSTRING(exe_number FROM 'E(\d+)') AS INTEGER) ASC;

说明 #

• 日期转换时，%b（MySQL）和Mon（PostgreSQL）会自动识别英文月份缩写（如Aug、Jul），需确保数据库语言设置支持英文月份解析。
• 提取迭代号时，通过字符串函数分离出E后的数字并转换为数值类型，避免按字符串规则排序（如E10排在E2前面的问题）。

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