如何在 mySQL 中定义自定义 ORDER BY 订单
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9378613/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to define a custom ORDER BY order in mySQL
提问by Muleskinner
In MySQL how do I define a custom sorting order.
在 MySQL 中如何定义自定义排序顺序。
To try to explain what I want consider this table:
为了解释我想要什么，请考虑这张表：
ID Language Text
0 ENU a
0 JPN b
0 DAN c
1 ENU d
1 JPN e
1 DAN f
2 etc...
here I want to return all rows sorted by Language and ascending ID so that Language = ENU comes first, then JPN and lastly DAN.
在这里，我想返回按语言和升序 ID 排序的所有行，以便语言 = ENU 首先出现，然后是 JPN，最后是 DAN。
The result should be: a,d,b,e,c,f etc.
结果应该是：a、d、b、e、c、f 等。
Is this even possible?
这甚至可能吗？
回答by Mchl
MySQL has a handy function called FIELD()which is excellent for tasks like this.
MySQL 有一个方便的函数调用FIELD()，它非常适合这样的任务。
ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID
ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID
Note however, that
但是请注意，
It makes your SQL less portable, as other DBMSs might not have such function
When your list of languages (or other values to sort by) gets much longer, it's better to have a separate table with sortorder column for them, and join it to your queries for ordering.
它使您的 SQL 的可移植性降低，因为其他 DBMS 可能没有这样的功能
当您的语言列表（或其他要排序的值）变得更长时，最好为它们创建一个带有 sortorder 列的单独表，并将其加入您的查询以进行排序。
回答by ruakh
If those are the only three values, then you can use a CASEexpression:
如果这些是仅有的三个值，那么你可以使用一个CASE表达式：
ORDER BY `ID`,
CASE `Language`
WHEN 'ENU' THEN 1
WHEN 'JPN' THEN 2
WHEN 'DAN' THEN 3
END
(If there could be other values, then you may want to add some extra logic to keep the ordering consistent; for example, you might add ELSE 4to that CASEexpression, and then order by Languageitself as the third ordering criterion:
（如果可能有其他值，那么您可能需要添加一些额外的逻辑以保持排序一致；例如，您可能会添加ELSE 4到该CASE表达式中，然后Language自行排序作为第三个排序标准：
ORDER BY `ID`,
CASE `Language`
WHEN 'ENU' THEN 1
WHEN 'JPN' THEN 2
WHEN 'DAN' THEN 3
ELSE 4
END,
`Language`
)
)
回答by Simon at My School Portal
You have a couple of options offhand, the first is to change Language to be ENUM (assuming this is possible, and you only expect a few variations)
您有几个选项，第一个是将 Language 更改为 ENUM（假设这是可能的，并且您只希望有一些变化）
If you specify it as ENUM('ENU','JPN','DAN')then ORDER Language ASCwill order in the order you specify.
如果指定其为ENUM('ENU','JPN','DAN')随后ORDER Language ASC将订购您指定的顺序。
The second will involve a case somewhere, i.e.
第二个将涉及某个地方的案例，即
SELECT * FROM table
ORDER BY CASE Language
WHEN 'ENU' THEN 3
WHEN 'JPN' THEN 2
WHEN 'DAN' THEN 1
ELSE 0
END DESC, ID ASC
Performance-wise the ENUM method will return faster results, but be more hassle if you need to add more languages. A third option would be to add a normalisation table for the Languages however that may be overkill in this instance.
在性能方面，ENUM 方法将返回更快的结果，但如果您需要添加更多语言，则会更加麻烦。第三种选择是为语言添加规范化表，但是在这种情况下这可能有点过头了。
回答by Prahlad
For Yii2 framework we cab achieve by following way
对于 Yii2 框架，我们通过以下方式实现
Project::find()
->orderBy([new Expression('FIELD(pid_is_t_m,2,0,1)'),'task_last_work'=> SORT_ASC])
->all();
