首先按特定字段值排序 [英] Ordering by specific field value first
问题描述
我有一个包含3列的表格:
I have a table with 3 columns:
id | name | priority
--------------------
1 | core | 10
2 | core | 9
3 | other | 8
4 | board | 7
5 | board | 6
6 | core | 4
我想使用priority
对结果集进行排序,但是首先那些具有name=core
的行,即使它们的优先级较低.结果应该像这样
I want to order the result set using priority
but first those rows that have name=core
even if have lower priority. The result should look like this
id | name | priority
--------------------
6 | core | 4
2 | core | 9
1 | core | 10
5 | board | 6
4 | board | 7
3 | other | 8
推荐答案
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:
首先,我很确定这是仅mysql的功能-问题被标记为mysql,但您永远不知道.
First, I'm pretty sure this is mysql-only functionality - the question is tagged mysql, but you never know.
第二,注意FIELD()
的工作方式:它返回值的基于索引的索引-在FIELD(priority, "core")
的情况下,如果"core"为价值.如果该字段的值不在列表中,则返回 zero .这就是为什么DESC
是必需的,除非您指定所有可能的值.
Second, pay attention to how FIELD()
works: it returns the one-based index of 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 DESC
is necessary unless you specify all possible values.
这篇关于首先按特定字段值排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!