以预定义的顺序返回查询结果 [英] Returning query results in predefined order

问题描述

是否可以以预定顺序执行SELECT语句.选择ID 7,2,5,9和8 并按顺序返回它们，仅基于ID字段?

Is it possible to do a SELECT statement with a predetermined order, ie. selecting IDs 7,2,5,9 and 8 and returning them in that order, based on nothing more than the ID field?

这两个语句以相同的顺序返回它们:

Both these statements return them in the same order:

SELECT id FROM table WHERE id in (7,2,5,9,8) 

SELECT id FROM table WHERE id in (8,2,5,9,7)

推荐答案

我认为这不可能，但发现此处的博客条目似乎符合您的要求:

I didn't think this was possible, but found a blog entry here that seems to do the type of thing you're after:

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"7,2,5,9,8");

将给出不同的结果

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"8,2,5,9,7");

FIND_IN_SET返回给定的第二个参数中id的位置，因此对于上面的第一种情况，id中的7在集合中的位置1，在2中的位置2，依此类推-mysql内部得出类似的结果

FIND_IN_SET returns the position of id in the second argument given to it, so for the first case above, id of 7 is at position 1 in the set, 2 at 2 and so on - mysql internally works out something like

id | FIND_IN_SET
---|-----------
7  | 1
2  | 2
5  | 3

然后按FIND_IN_SET的结果进行排序.

then orders by the results of FIND_IN_SET.

这篇关于以预定义的顺序返回查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！