mysql排序的灵活使用(复杂排序)
原表数据
- 通过状态排序,顺序依次为4,5,3,2,1
select * from cm_demo order by status = 4 DESC,status DESC
- 通过状态复杂排序,顺序依次为3,5,4,1,2
select * from cm_demo
order by status = 3 DESC, status = 5 DESC, status = 4 DESC, status ASC
- 通过状态排序顺序依次为3,5,4,1,2,并且要求在这个基础上按created_date倒序
select * from cm_demo
order by status = 3 DESC, status = 5 DESC, status = 4 DESC, status ASC, created_date DESC
- 通过状态排序,顺序依次为3,5,4,2,1,并且要求在这个基础上时间排序:
status = 3;created_date desc
status = 5;created_date asc
status = 4;created_date asc
status = 2;created_date asc
status = 1;created_date asc
select * from cm_demo
order by status = 3 DESC, status DESC,
case WHEN status = 3 THEN created_date END DESC, created_date ASC