在MySQL中,如何定义自定义排序顺序.
In MySQL how do I define a custom sorting order.
要尝试解释我要考虑的这张表,请执行以下操作:
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...在这里,我想返回按语言和升序ID排序的所有行,以便首先出现Language = ENU,然后是JPN,最后是DAN.
here I want to return all rows sorted by Language and ascending ID so that Language = ENU comes first, then JPN and lastly DAN.
结果应为:a,d,b,e,c,f等.
The result should be: a,d,b,e,c,f etc.
这有可能吗?
推荐答案MySQL有一个方便的函数,称为 FIELD() ,非常适合执行此类任务.
MySQL has a handy function called FIELD() which is excellent for tasks like this.
ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID
请注意,
它使您的SQL的可移植性降低,因为其他DBMS可能没有这种功能
It makes your SQL less portable, as other DBMSs might not have such function
当您的语言列表(或其他要排序的值)长得多时,最好有一个单独的带有sortorder列的表,然后将其加入查询中进行排序.
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.
更多推荐
如何在MySQL中定义自定义ORDER BY顺序
发布评论