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

本文介绍了如何在MySQL中定义自定义ORDER BY顺序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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