在选择语句"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/zh-CN/string-functions.html#function_field

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