如何在 mySQL 中定义自定义 ORDER BY 订单

2021-11-20 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 排序的所有行，以便语言 = 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.

相关文章