在选择语句“in"中按值的顺序排序mysql中的子句 [英] Sort by order of values in a select statement "in" clause in mysql

问题描述

我正在从具有整数 id 值的大表(数百万行)中选择一组帐户记录.从某种意义上说，这是查询的基础.我正在做的是构建一个大的逗号分隔列表，并将其作为in"子句传递到查询中.现在的结果是完全无序的.我想要做的是按照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.

我假设相反，我必须建立一个临时表并进行连接，我想避免这种情况，但可能无法做到.

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.

想法?现在查询的大小上限为每个大约 60k，因为我们试图限制输出大小，但它可以是任意大的，从实际的角度来看，这可能会排除in"查询，如果不是物理的.

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.

提前致谢.

推荐答案

其实这样更好:

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

这里是 FIELD 文档:

heres the FIELD documentation:

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

这篇关于在选择语句“in"中按值的顺序排序mysql中的子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！