我可以在MySql中同时按列名和case语句排序吗？

I am trying to order by query results by 2 things like so

我试图通过2个类似的查询结果来排序

by "reservationStatus" and then by one of two fields depending on the reservationStatus.

通过“reservationStatus”然后根据reservationStatus由两个字段之一。

this is my mysql code

这是我的mysql代码

SELECT rs.reservationStatus,
DATE_FORMAT(rs.dateFrom, "%d-%m-%Y %h:%i %p") AS pickupTime,
DATE_FORMAT(rs.dateTo, "%d-%m-%Y %h:%i %p") AS dropoffTime,
cu.fullName,
ve.name FROM reservation AS rs 
INNER JOIN customers AS cu ON rs.CustomerID = cu.customerID
INNER JOIN vehicles AS ve ON rs.Vehicle_id = ve.Vehicles_id
WHERE  rs.dateFrom <= DATE_ADD(curdate() , INTERVAL 86399 SECOND) AND rs.reservationStatus IN (1,2)
ORDER BY  rs.reservationStatus, CASE WHEN rs.reservationStatus = 1 THEN rs.dateFrom ELSE rs.dateTo END ASC

That gives me a syntax error. However if I remove rs.reservationStatus, and leave the case statment there then it runs but i don't see the results ordered like it should.

这给了我一个语法错误。但是,如果我删除rs.reservationStatus,并在那里保留案例声明,那么它会运行,但我没有看到按照它应该排序的结果。

What i need is to sort my results like this

我需要的是像这样对结果进行排序

How can I get my query above to run correctly?

如何才能使上面的查询正确运行?

1 个解决方案

#1

"CASE" expressions produce some column result, based on expression derived from other column or columns content. Try rather

“CASE”表达式根据从其他列或列内容派生的表达式生成一些列结果。试试吧

SELECT 
 column1, 
 CASE WHEN ... THEN ... ELSE ... AS customcolumnname, 
 column2.sometable, 
 column3 .. 
FROM 
 mytable
ORDER BY 
 customcolumnname ASC, 
 column1 DESC

or similar.