按mysql中select语句“in”子句中的值顺序排序

I'm selecting a set of account records from a large table (millions of rows) with integer id values. As basic of a query as one gets, in a sense. What I'm doing us building a large comma separated list, and passing that into the query as an "in" clause. Right now the result is completely unordered. What I'd like to do is get the results back in the order of the values in the "in" clause.

我正在从具有整数id值的大表（数百万行）中选择一组帐户记录。从某种意义上说，作为一个人获得的查询的基础。我正在做什么构建一个大的逗号分隔列表，并将其作为“in”子句传递给查询。现在结果是完全无序的。我想要做的是按照“in”子句中的值的顺序返回结果。

I assume instead I'll have to build a temporary table and do a join instead, which I'd like to avoid, but may not be able to.

我假设我将不得不构建一个临时表并进行连接，我想避免，但可能无法做到。

Thoughts? The size of the query right now is capped at about 60k each, as we're trying to limit the output size, but it could be arbitrarily large, which might rule out an "in" query anyway from a practical standpoint, if not a physical one.

思考？现在查询的大小限制在每个约60k，因为我们试图限制输出大小，但它可能是任意大的，这可能排除了从实际角度来看的“in”查询，如果不是物理的。

Thanks in advance.

提前致谢。

4 个解决方案

#1

Actually, this is better:

实际上，这更好：

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIELD(id,5,2,6,8,12,1);

heres the FIELD documentation:

继承了FIELD文档：

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

#2

A bit of a trick....

有点诡计....

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIND_IN_SET(id,'5,2,6,8,12,1') DESC;

note that the list of ID's in the find_in_set is a string, so its quoted. Also note that without DESC, they results are returned in REVERSE order to what the list specified.

请注意，find_in_set中的ID列表是一个字符串，因此引用它。另请注意，如果没有DESC，它们的结果将以REVERSE顺序返回到指定的列表。

#3

If your query is 60K, that's a sign that you're doing it the wrong way.

如果您的查询是60K，那就表明您正在以错误的方式执行此操作。

There is no other way to order the result set than by using an ORDER BY clause. You could have a complicated CASE clause in your order by listing all the elements in your IN clause again, but then your query would probably be 120K.

除了使用ORDER BY子句之外，没有其他方法可以对结果集进行排序。通过再次列出IN子句中的所有元素，您的订单中可能会有一个复杂的CASE子句，但您的查询可能是120K。

I know you don't want to, but you should put the values in the IN clause in a table or a temporary table and join with it. You can also include a SortOrder column in the temporary table, and order by that. Databases like joins. Doing it this way will help your query to perform well.

我知道你不想这样做，但是你应该把IN子句中的值放在表或临时表中并加入它。您还可以在临时表中包含SortOrder列，并按此顺序排序。像连接这样的数据库。这样做可以帮助您的查询表现良好。

#4

You're first query surely uses an order by clause. So, you could just do a join, and use the same order by clause.

你是第一个查询肯定使用order by子句。因此，您可以只进行连接，并使用相同的order by子句。

For example, if this was your first query

例如，如果这是您的第一个查询

SELECT customer_id
  FROM customer  
 WHERE customer_id BETWEEN 1 AND 100 
ORDER
    BY last_name

And this was your second query

这是你的第二个问题

SELECT inventory_id
  FROM rental
 WHERE customer_id in (...the ordered list...)

Combined would be

合并将是

SELECT r.inventory_id
  FROM rental r
INNER
  JOIN customer c
    ON r.customer_id = c.customer_id    
 WHERE c.customer_id BETWEEN 1 AND 100 
ORDER
    BY c.last_name

#1