如何在 mySQL 中定义自定义 ORDER BY 顺序

2022-01-30 00:00:00 mysql

在 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

但请注意，

  1. 它会降低 SQL 的可移植性，因为其他 DBMS 可能没有这样的功能

  1. 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.

相关文章