MySQL 多个 ORDER BY 基于条件

Question

我有如下查询，当我仅按 end_time 订购表格时，该查询有效:

有效

SELECT * FROM table1 WHERE ... ORDER BY field(table1.status, 'c1','a1', 'b1', 'e1',
'd1') asc, IF(table1.status = 'f1', table1.end_time, '') asc LIMIT 20 OFFSET 0;

但是，如果我想在条件排序中再添加一列，例如:

SELECT * FROM table1 WHERE ... ORDER BY field(table1.status, 'c1', 'a1', 'b1', 'e1',
'd1') asc, IF(table1.status = 'f1', (table1.end_time, table1.start_time), '') asc
LIMIT 20 OFFSET 0;

它给了我:

ERROR 1241 (21000): Operand should contain 1 column(s)

但是，以下不带 IF 的查询有效:

SELECT * FROM table1 WHERE ... ORDER BY field(table1.status, 'c1', 'a1', 'b1', 'e1',
'd1') asc, table1.end_time, table1.start_time asc LIMIT 20 OFFSET 0;

如何使用 IF

实现与上面相同的查询

Answer 1

如果你只是想在 table1.start_time 以及 table1.status = 'f1' 时订购，你只需要添加另一个 IF:

SELECT * 
FROM table1 
WHERE ... 
ORDER BY field(table1.status, 'c1','a1', 'b1', 'e1','d1') asc,
    IF(table1.status = 'f1', table1.end_time, '') asc,
    IF(table1.status = 'f1', table1.start_time, '') asc
LIMIT 20 OFFSET 0;