MySQL基于状态的多条件排序扩展实现问题咨询

Let's break down what's going wrong and how to fix it.

The issue with your second query is that the IF() function can only return a single value—you can't pass a comma-separated pair of fields like (table1.end_time, table1.start_time) as its return value. MySQL doesn't recognize that syntax for conditional sorting across multiple fields.

Correct Implementation #

Instead of trying to cram both fields into one IF() call, split them into separate entries in your ORDER BY clause. This way, you can apply the conditional logic to each field individually:

SELECT * 
FROM table1 
WHERE ... 
ORDER BY 
    -- Keep your original custom status sorting
    field(table1.status, 'c1','a1', 'b1', 'e1', 'd1') asc,
    -- Sort by end_time only when status is 'f1'; use empty string for others to avoid affecting their order
    IF(table1.status = 'f1', table1.end_time, '') asc,
    -- Then sort by start_time only for 'f1' status records
    IF(table1.status = 'f1', table1.start_time, '') asc
LIMIT 20 OFFSET 0;

How This Works #

1. First, we sort all records using your custom FIELD() logic for the status column—this preserves your original sorting priority.
2. Next, for records where status = 'f1', we sort them by end_time in ascending order. For all other records, we use an empty string (matching your original approach) so this field doesn't alter their relative order.
3. Finally, we apply the same conditional logic to start_time: only f1 records get sorted by this field, ensuring they're ordered first by end_time, then by start_time.

If you prefer using CASE WHEN (which can be more readable for complex conditions), here's an equivalent version:

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

Either approach will correctly apply the multi-field sorting only to records with status = 'f1' while maintaining your original sorting behavior for all other records.

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