如何在MySQL中定义自定义ORDER BY顺序 [英] How to define a custom ORDER BY order in 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有一个方便的函数，称为

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.

这篇关于如何在MySQL中定义自定义ORDER BY顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！