首先按特定字段值排序 [英] 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

推荐答案

还有 MySQL FIELD 函数.

There's also the MySQL FIELD function.

如果您想对所有可能的值进行完整排序:

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

不过这里有一些警告:

首先，我很确定这是仅限 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"是值，它会返回1.如果该字段的值不在列表中，则返回零.这就是为什么 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屋！