☰

MySQLでカスタムORDER BY順序を定義する方法 質問する

tag-icon • tag-icon mysql
MySQLでカスタムORDER BY順序を定義する方法 質問する

MySQL でカスタムのソート順序を定義するにはどうすればいいですか。

私が言いたいことを説明するために、次の表を検討してください。

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.

The result should be: a,d,b,e,c,f etc.

Is this even possible?

ベストアンサー1

MySQL has a handy function called FIELD() which is excellent for tasks like this.

ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID

Note however, that

  1. It makes your SQL less portable, as other DBMSs might not have such function

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

おすすめ記事