MySQL中的条件排序? [英] Conditional sorting in MySQL?

问题描述

我有包含3个字段的任务"表:

I have "tasks" table with 3 fields:

1. 日期
2. 优先级(0,1,2)
3. 完成(0,1)

我要实现的是将整个表按完成"标志排序，未完成的任务应按优先级排序，而已完成的任务应按日期排序:

What I am trying to achieve is with the whole table sorted by done flag, tasks that are not done should be sorted by priority, while tasks that are done should be sorted by date:

1. 按完成asc的顺序从任务中选择*
2. 如果完成= 0，则按优先级依次递减
3. 如果完成= 1，则按日期顺序另外排序

是否可以在没有工会的MySQL中做到这一点?

Is it possible to do this in MySQL without unions?

谢谢.

推荐答案

您可以尝试ORDER BY (done asc, aux desc)，其中用CASE计算aux会根据done的值产生优先级或日期(您可能有将它们转换为相同的类型以适合相同的表达式，例如将日期转换为合适的整数天数.

You could try ORDER BY (done asc, aux desc) where aux is computed with a CASE to yield either priority or date based on the value of done (you may have to cast them to the same type to fit in the same expression, e.g. cast the date to a suitable integer day number).

例如:

SELECT * FROM tab
ORDER BY done desc,
         case done
             when 0 then prio 
             else to_days(thedate)
         end desc;

这篇关于MySQL中的条件排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！