MySQL 首先按特定字段值排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14104055/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Ordering by specific field value first
提问by Omid
I have a table with 3 columns:
我有一个包含 3 列的表：
id | name | priority
--------------------
1 | core | 10
2 | core | 9
3 | other | 8
4 | board | 7
5 | board | 6
6 | core | 4
I want to order the result set using prioritybut first those rows that have name=coreeven if have lower priority. The result should look like this
我想对结果集进行排序，priority但首先使用那些name=core即使优先级较低的行。结果应该是这样的
id | name | priority
--------------------
6 | core | 4
2 | core | 9
1 | core | 10
5 | board | 6
4 | board | 7
3 | other | 8
回答by Nerdmaster
There's also the MySQL FIELDfunction.
还有MySQLFIELD函数。
If you want complete sorting for all possible values:
如果要对所有可能的值进行完整排序：
SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core", "board", "other")
If you only care that "core" is first and the other values don't matter:
如果您只关心“核心”是第一位而其他值无关紧要：
SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC
If you want to sort by "core" first, and the other fields in normal sort order:
如果要先按“核心”排序，然后按正常排序顺序对其他字段进行排序：
SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC, priority
There are some caveats here, though:
不过，这里有一些警告：
First, I'm pretty sure this is mysql-only functionality - the question is tagged mysql, but you never know.
首先，我很确定这是仅限 mysql 的功能 - 问题被标记为 mysql，但您永远不知道。
Second, pay attention to how FIELD()works: it returns the one-based indexof the value - in the case of FIELD(priority, "core"), it'll return 1 if "core" is the value. If the value of the field is not in the list, it returns zero. This is why DESCis necessary unless you specify all possible values.
其次，注意FIELD()工作原理：它返回值的从1 开始的索引- 在 的情况下FIELD(priority, "core")，如果“核心”是值，它将返回 1。如果该字段的值不在列表中，则返回零。这就是为什么DESC有必要除非您指定所有可能的值。
回答by juergen d
Generally you can do
一般你可以做
select * from your_table
order by case when name = 'core' then 1 else 2 end,
priority
Especially in MySQL you can also do
特别是在 MySQL 中你也可以这样做
select * from your_table
order by name <> 'core',
priority
Since the result of a comparision in MySQL is either 0or 1and you can sort by that result.
由于 MySQL 中的比较结果是 0or 1，您可以按该结果进行排序。
回答by mellamokb
One way to give preference to specific rows is to add a large number to their priority. You can do this with a CASEstatement:
优先考虑特定行的一种方法是在其优先级中添加大量数字。你可以用一个CASE语句来做到这一点：
select id, name, priority
from mytable
order by priority + CASE WHEN name='core' THEN 1000 ELSE 0 END desc
回答by Vojtech Vitek
This works for me using Postgres 9+:
这对我使用 Postgres 9+ 有效：
SELECT *
FROM your_table
ORDER BY name = 'core' DESC, priority DESC
回答by Icarus
One way is this:
一种方法是这样的：
select id, name, priority from table a
order by case when name='core' then -1 else priority end asc, priority asc
回答by meiy arul
SELECT * FROM cars_new WHERE status = '1' and car_hide !='1' and cname IN ('Executive Car','Saloon','MPV+','MPV5') ORDER BY FIELD(cname, 'Executive Car', 'Saloon','MPV+','mpv5')
回答by Saquib Azam
Use this:
用这个：
SELECT *
FROM tablename
ORDER BY priority desc, FIELD(name, "core")
回答by Madhuka Dilhan
do this:
做这个：
SELECT * FROM table ORDER BY column `name`+0 ASC
Appending the +0 will mean that:
附加 +0 意味着：
0, 10, 11, 2, 3, 4
becomes :
变成：
0, 2, 3, 4, 10, 11
