mysql SQL：首先要特定项目，然后对其余项目进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5417980/
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
mysql SQL: specific item to be first and then to sort the rest of the items
提问by ufk
Lets say I have the table below.
假设我有下表。
I want to get all the friends, but I want the id 5 to be the first item in the list. I don't care about the order that I receive the rest of the items.
我想得到所有的朋友，但我想让 id 5 成为列表中的第一项。我不在乎我收到其余物品的顺序。
The desired query result will be:
所需的查询结果将是：
friends
-------
id name
5 nahum
1 moshe
2 haim
3 yusuf
4 gedalia
6 dana
How can I do this?
我怎样才能做到这一点？
using Mysql 5.1.x.
使用 MySQL 5.1.x。
Thanks!
谢谢！
回答by RichardTheKiwi
select id,name
from friends
order by id=5 desc
(given you don't care about order of the rest, otherwise, e.g. rest by id asc)
（假设您不关心其余的顺序，否则，例如按 id asc 休息）
select id,name
from friends
order by id=5 desc, id asc
回答by Dumitrescu Bogdan
Try this:
尝试这个：
select id,name
from friends
order by case when id=5 then -1 else id end
if you have more then one you can do:
如果你有更多然后你可以这样做：
select id,name
from friends
order by case when id in (5,15,25) then -1 else id end,id
回答by Amadan
I can't access a MySQL now to test, so it might be reversed... but you can use the fact that Booleans also sort, and that you can have several sort fields.
我现在无法访问 MySQL 进行测试，因此它可能会被颠倒……但是您可以使用布尔值也可以排序的事实，并且您可以有多个排序字段。
SELECT ... ORDER BY id != 5, id
(you might have to write id = 5, I can't remember if TRUEs sort before or after FALSEs.)
（你可能不得不写id = 5，我不记得 TRUE 是在 FALSE 之前还是之后排序。）
EDIT: Oh, I just read that you don't care about the order of the rest, in which case I heartily recommend @Richard's answer.
编辑：哦，我刚刚读到您不关心其余部分的顺序，在这种情况下，我衷心推荐@Richard 的回答。
回答by Ucello
If you want to do the same for UNION query, for example if you have:
如果您想对 UNION 查询执行相同的操作，例如，如果您有：
select id,name
from friends
UNION
select id,name
from friends
order by id=5 desc
... you would get an exception in PostgreSQL:
...你会在 PostgreSQL 中得到一个异常：
Only result column names can be used, not expressions or functions. HINT: Add the expression/function to every SELECT, or move the UNION into a from clause
只能使用结果列名称，不能使用表达式或函数。提示：将表达式/函数添加到每个 SELECT，或将 UNION 移动到 from 子句中
To get around this, you would use the following:
要解决此问题，您可以使用以下方法：
select id,name, (id=5) AS is_five
from friends
UNION
select id,name, (id=5) AS is_five
from friends
order by is_five DESC, id DESC
The expression (id=5) would return 't' OR 'f', depending on whether your column value is equal or not to the expected value (5), so the order by would first order the 't' columns, then the rest.
表达式 (id=5) 将返回 't' OR 'f'，具体取决于您的列值是否等于预期值 (5)，因此 order by 将首先对 't' 列进行排序，然后是休息。
回答by MontyPython
You should use MySQL's ORDER BY FIELD clause to solve this. Although, the answer has been accepted on this, here's a better solution.
您应该使用 MySQL 的 ORDER BY FIELD 子句来解决这个问题。虽然答案已被接受，但这里有一个更好的解决方案。
select 1 id, 'Zeta' order_col union all
select 2 id, 'Alpha' order_col union all
select 3 id, 'Gamma' order_col union all
select 4 id, 'Phi' order_col union all
select 5 id, 'Delta' order_col union all
select 6 id, 'Delta' order_col union all
select 7 id, 'Alpha' order_col union all
select 8 id, 'Gamma' order_col union all
select 9 id, 'Zeta' order_col union all
select 10 id, 'Phi' order_col
order by field (order_col, 'Alpha', 'Gamma', 'Phi', 'Delta', 'Zeta'), id;
This is better than
这比
- id=something, order by id asc
- order by case when something then 1 when something_else then 2 end desc
- id=something, 按 id asc 排序
- 按情况排序 when something then 1 when something_else then 2 end desc
回答by Jeffrey Neo
You can use field()in MySQL.
您可以field()在 MySQL 中使用。
select id,name from friends order by field(id,5,id)
The 1st parameter in field() means the field you want to sort with, the rest is ordering.
field() 中的第一个参数表示您要排序的字段，其余的是排序。
So 5 will be sort first, and the rest from id (without 5). You can do like field(id,5,1,3,id)if you want 5,1,3 to be in front.
所以 5 将首先排序，其余的从 id （没有 5）。field(id,5,1,3,id)如果您希望 5,1,3 排在前面，您可以这样做。
5 can be choose to sort at last by field(id,id,5). The 2nd id will exclude 5 from it also.
5 最后可以选择排序方式field(id,id,5)。第二个 id 也会从中排除 5。
回答by Ilya Kogan
This is a little ugly because it has code duplication, but it does the trick:
这有点难看，因为它有代码重复，但它确实有效：
select .... where id = 5
union
select .... where not id = 5
