mysql - 如何在Mysql中使用order by案例？

Question

如果 t_date(column_name) 是今天的日期，则

select * from `schedules` 
ORDER BY available_seats <= 0 , STR_TO_DATE(departure_time,'%h:%i%p');

其他

select * from `schedules`
ORDER BY (available_seats <= 0 && (STR_TO_DATE(departure_time,'%h:%i%p') >= TIME(NOW()))), (STR_TO_DATE(departure_time,'%h:%i%p') <= TIME(NOW())), STR_TO_DATE(departure_time,'%h:%i%p');

结束

查询 1 适用于 t_date = DATE(now())

查询 2 适用于 t_date != DATE(now())

如何在单个查询中以 order by 为条件进行查询？

Answer 1

您可以像下面一样使用CASE

SELECT * 
FROM `schedules` 
ORDER BY 
available_seats <= 0 ,
CASE WHEN t_date <> CURRENT_DATE()  AND (available_seats <= 0 && (STR_TO_DATE(departure_time,'%h:%i%p') >= TIME(NOW())))
THEN
(STR_TO_DATE(departure_time,'%h:%i%p') <= TIME(NOW()))
WHEN t_date = CURRENT_DATE() 
THEN STR_TO_DATE(departure_time,'%h:%i%p')
ELSE STR_TO_DATE(departure_time,'%h:%i%p') END,
STR_TO_DATE(departure_time,'%h:%i%p')