MySQL 按给定顺序选择 WHERE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3799935/
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 Select WHERE IN given order
提问by kentor
I have something like
我有类似的东西
SELECT * FROM table WHERE id IN (118,17,113,23,72);
If I just do this it returns the rows in ID ascending order. Is there a way to get back the rows in the order given in the IN statement?
如果我只是这样做，它会以 ID 升序返回行。有没有办法按照 IN 语句中给出的顺序取回行？
回答by nico
You should use "ORDER BY FIELD". So, for instance:
您应该使用“按字段排序”。因此，例如：
SELECT * FROM table WHERE id IN (118,17,113,23,72)
ORDER BY FIELD(id,118,17,113,23,72)
回答by Brian
Try using FIND_IN_SET:
尝试使用FIND_IN_SET：
SELECT * FROM table WHERE id IN (118,17,113,23,72)
ORDER BY FIND_IN_SET(id, '118,17,113,23,72');
回答by egrunin
You can create a temp table with two columns (ID, order_num):
您可以创建一个包含两列（ID、order_num）的临时表：
ID order_num
118 1
17 2
113 3
23 4
72 5
Then join:
然后加入：
SELECT * from table
INNER JOIN #temp_table
ON table.id = #temp_table.id
Notice that you can drop the INclause.
请注意，您可以删除该IN子句。
Sometimes I actually create a permanent table, because then when the client inevitablychanges their mind about the ordering, I don't have to touch the code, just the table.
有时我实际上创建了一个永久表格，因为当客户不可避免地改变他们对订购的看法时，我不必接触代码，只需接触表格。
Edit
编辑
The answer using ORDER BY FIELD()(which I didn't know about) is probably what you want.
使用ORDER BY FIELD()（我不知道）的答案可能就是您想要的。
回答by bumperbox
this is the first thing that pops to mind. note sql is untested, you might need to check correct syntax
这是首先想到的事情。注意 sql 未经测试，您可能需要检查正确的语法
its a bit cumbersome, but might do the trick
它有点麻烦，但可能会成功
select * from table where id = 118
union
select * from table where id = 17
union
.... and so on
回答by Mitch Dempsey
I think if you did a UNIONquery with each select, it might return it in the order.
我认为如果您对UNION每个选择进行查询，它可能会按顺序返回它。
SELECT * FROM table WHERE id=118
UNION
SELECT * FROM table WHERE id=17
...
Ugly, but I think it will work.
丑陋，但我认为它会奏效。
回答by David Espart
One option is to use UNION:
一种选择是使用 UNION：
SELECT * FROM table WHERE id = 118
UNION
SELECT * FROM table WHERE id = 17
UNION
SELECT * FROM table WHERE id = 113
...
回答by Guffa
You can create a number to sort on based on the id values:
您可以创建一个数字以根据 id 值进行排序：
select *
from table
where id in (118,17,113,23,72)
order by
case id
when 118 then 1
when 17 then 2
when 133 then 3
when 23 then 4
when 72 then 5
end
