MySQL复杂排序需求：按日期降序、当日时间升序排列数据

我有一张名为test的表，表结构及数据如下：

ID | NAME | DATETIME
---+------+-----------------------
1  | a1   | 2023-05-01 01:00:00
2  | a2   | 2023-05-01 02:00:00
3  | a3   | 2023-05-01 03:00:00
4  | b1   | 2023-05-02 01:00:00
5  | b2   | 2023-05-02 02:00:00
6  | b3   | 2023-05-02 03:00:00

执行简单的降序查询：

SELECT * FROM `test` ORDER by `DATETIME` DESC

得到的结果ID顺序是6、5、4、3、2、1，但我需要按日期从新到旧排序，同时每个日期内按时间升序排列，即结果ID顺序为4、5、6、1、2、3。

曾尝试用PHP生成临时数组重新排序，但因查询带有LIMIT分页参数，这种方案不合适，请求合适的MySQL查询写法。

解决方案 #

可以通过多字段排序实现需求，先按日期部分降序排列，再按当日时间升序排列，对应的SQL语句如下：

SELECT * FROM `test`
ORDER BY DATE(`DATETIME`) DESC, `DATETIME` ASC;

说明 #

• DATE(DATETIME) 提取字段的日期部分，按该值降序排序，确保较新的日期排在前列；
• 第二个排序条件DATETIME ASC会在同一日期范围内，按时间从早到晚升序排列，完全匹配需求。

如果需要避免函数调用可能带来的索引失效问题，也可以使用等价写法：

SELECT * FROM `test`
ORDER BY `DATETIME` DESC, TIME(`DATETIME`) ASC;

两种写法逻辑一致，第一种更直观，适合大多数场景。

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