Mysql 按特定 ID 值排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8322849/
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 order by specific ID values
提问by Jerry2
Is it possible to sort in mysql by "order by" using predefined set of column values (ID) like: order by (ID=1,5,4,3) so I would get record 1, 5, 4, 3 in that order out?
是否可以使用预定义的一组列值（ID）按“order by”在mysql中进行排序，例如：order by (ID=1,5,4,3) 所以我会得到记录 1, 5, 4, 3下单？
UPDATE:About abusing mysql ;-) I have to explain why I need this...
更新：关于滥用 mysql ;-) 我必须解释为什么我需要这个......
I want my records change sort randomly every 5 minutes. I have a cron task to do the update table to put different, random sort order in it. There is just one problem! PAGINATION. I will have a visitor who comes to my page and I give him first 20 results. He will wait 6 minutes and go to page 2 and he will have wrong results as the sort order had allready changed.
我希望我的记录每 5 分钟随机更改一次。我有一个 cron 任务来执行更新表以在其中放置不同的随机排序顺序。只有一个问题！分页。我会有一个访问者访问我的页面，我会给他前 20 个结果。他将等待 6 分钟并转到第 2 页，并且由于排序顺序已经改变，他将得到错误的结果。
So I thought that if he comes to my site I put all the ID's to a session and when he is in page 2 he get's the correct records out even if the sorting allready changed.
所以我想，如果他来到我的网站，我会把所有的 ID 放到一个会话中，当他在第 2 页时，即使排序已经改变，他也会得到正确的记录。
Is there any other way, better, to do this?
有没有其他更好的方法来做到这一点？
回答by Manjula
You can use ORDER BY and FIELD function. See http://lists.mysql.com/mysql/209784
您可以使用 ORDER BY 和 FIELD 功能。见http://lists.mysql.com/mysql/209784
SELECT * FROM table ORDER BY FIELD(ID,1,5,4,3)
It uses Field()function, Which "Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found" according to the documentation. So actually you sort the result set by the return value of this function which is the index of the field value in the given set.
它使用Field()函数，根据文档“返回 str1、str2、str3、...列表中 str 的索引（位置）。如果找不到 str 则返回 0”。所以实际上你通过这个函数的返回值对结果集进行排序，返回值是给定集合中字段值的索引。
回答by NPE
You should be able to use CASEfor this:
您应该能够为此使用CASE：
ORDER BY CASE id
WHEN 1 THEN 1
WHEN 5 THEN 2
WHEN 4 THEN 3
WHEN 3 THEN 4
ELSE 5
END
回答by Jan Dragsbaek
On the official documentation for mysql about ORDER BY, someone has posted that you can use FIELDfor this matter, like this:
在关于ORDER BY 的mysql 官方文档中，有人发布了您可以FIELD用于此问题的帖子，如下所示：
SELECT * FROM table ORDER BY FIELD(id,1,5,4,3)
This is untested code that in theory should work.
这是未经测试的代码，理论上应该可以工作。
回答by Bjoern
There's another way to solve this. Add a separate table, something like this:
还有一种方法可以解决这个问题。添加一个单独的表，如下所示：
CREATE TABLE `new_order` (
`my_order` BIGINT(20) UNSIGNED NOT NULL,
`my_number` BIGINT(20) NOT NULL,
PRIMARY KEY (`my_order`),
UNIQUE KEY `my_number` (`my_number`)
) ENGINE=INNODB;
This table will now be used to define your own order mechanism.
此表现在将用于定义您自己的订单机制。
Add your values in there:
在那里添加您的值：
my_order | my_number
---------+----------
1 | 1
2 | 5
3 | 4
4 | 3
...and then modify your SQL statement while joining this new table.
...然后在加入这个新表时修改您的 SQL 语句。
SELECT *
FROM your_table AS T1
INNER JOIN new_order AS T2 on T1.id = T2.my_number
WHERE ....whatever...
ORDER BY T2.my_order;
This solution is slightly more complex than other solutions, but using this you don't have to change your SELECT-statement whenever your order criteriums change - just change the data in the order table.
这个解决方案比其他解决方案稍微复杂一些，但是使用它您不必SELECT在订单标准发生变化时更改-statement - 只需更改订单表中的数据。
回答by Bruno Mangini Alves
SELECT * FROM table ORDER BY id='8' DESC, id='5' DESC, id='4' DESC, id='3' DESC
If I had 10 registries for example, this way the ID 1, 5, 4 and 3 will appears first, the others registries will appears next.
例如，如果我有 10 个注册表，这样 ID 1、5、4 和 3 将首先出现，其他注册表将随后出现。
Normal exibition 1 2 3 4 5 6 7 8 9 10
正常展示 1 2 3 4 5 6 7 8 9 10
With this way
用这种方式
8 5 4 3 1 2 6 7 9 10
8 5 4 3 1 2 6 7 9 10
回答by Rajesh Kharatmol
If you need to order a single id first in the result, use the id.
如果您需要在结果中首先对单个 id 进行排序，请使用该 id。
select id,name
from products
order by case when id=5 then -1 else id end
If you need to start with a sequence of multiple ids, specify a collection, similar to what you would use with an INstatement.
如果您需要从多个 id 的序列开始，请指定一个集合，类似于您在IN语句中使用的内容。
select id,name
from products
order by case when id in (30,20,10) then -1 else id end,id
回答by Hisham shahid
SELECT * FROM TABLE ORDER BY (columnname,1,2) ASC OR DESC
