MySQL 按 SQL IN() 子句中值的顺序排序

Ordering by the order of values in a SQL IN() clause

提问by Darryl Hein

I am wondering if there is away (possibly a better way) to order by the order of the values in an IN() clause.

我想知道是否有（可能是更好的方法）按 IN() 子句中值的顺序排序。

The problem is that I have 2 queries, one that gets all of the IDs and the second that retrieves all the information. The first creates the order of the IDs which I want the second to order by. The IDs are put in an IN() clause in the correct order.

问题是我有 2 个查询，一个获取所有 ID，第二个获取所有信息。第一个创建我希望第二个排序的 ID 的顺序。ID 以正确的顺序放置在 IN() 子句中。

So it'd be something like (extremely simplified):

所以它会是这样的（非常简化）：

SELECT id FROM table1 WHERE ... ORDER BY display_order, name

SELECT name, description, ... WHERE id IN ([id's from first])

The issue is that the second query does not return the results in the same order that the IDs are put into the IN() clause.

问题是第二个查询返回的结果与 ID 放入 IN() 子句的顺序不同。

One solution I have found is to put all of the IDs into a temp table with an auto incrementing field which is then joined into the second query.

我发现的一个解决方案是将所有 ID 放入带有自动递增字段的临时表中，然后将其加入第二个查询中。

Is there a better option?

有更好的选择吗？

Note:As the first query is run "by the user" and the second is run in a background process, there is no way to combine the 2 into 1 query using sub queries.

注意：由于第一个查询是“由用户”运行的，而第二个查询是在后台进程中运行的，因此无法使用子查询将 2 个查询合并为 1 个查询。

I am using MySQL, but I'm thinking it might be useful to have it noted what options there are for other DBs as well.

我正在使用 MySQL，但我认为让它记录其他 DB 的选项可能会很有用。

回答by ???u

Use MySQL's FIELD()function:

使用 MySQL 的FIELD()功能：

SELECT name, description, ...
FROM ...
WHERE id IN([ids, any order])
ORDER BY FIELD(id, [ids in order])

FIELD()will return the index of the first parameter that is equal to the first parameter (other than the first parameter itself).

FIELD()将返回等于第一个参数的第一个参数的索引（第一个参数本身除外）。

FIELD('a', 'a', 'b', 'c')

FIELD('a', 'a', 'b', 'c')

will return 1

将返回 1

FIELD('a', 'c', 'b', 'a')

FIELD('a', 'c', 'b', 'a')

will return 3

将返回 3

This will do exactly what you want if you paste the ids into the IN()clause and the FIELD()function in the same order.

如果您以相同的顺序将 id 粘贴到IN()子句和FIELD()函数中，这将完全符合您的要求。

回答by Pradeep Singh

See following how to get sorted data.

请参阅以下如何获取排序数据。

SELECT ...
  FROM ...
 WHERE zip IN (91709,92886,92807,...,91356)
   AND user.status=1
ORDER 
    BY provider.package_id DESC 
     , FIELD(zip,91709,92886,92807,...,91356)
LIMIT 10

回答by John Nilsson

Two solutions that spring to mind:

想到的两个解决方案：

1. order by case id when 123 then 1 when 456 then 2 else null end asc

2. order by instr(','||id||',',',123,456,') asc

1. order by case id when 123 then 1 when 456 then 2 else null end asc

2. order by instr(','||id||',',',123,456,') asc

(instr()is from Oracle; maybe you have locate()or charindex()or something like that)

（instr()来自甲骨文；也许你有locate()或charindex()或类似的东西）

回答by Gulshan Prajapati

Ans to get sorted data.

Ans 获取排序数据。

SELECT ...
FROM ...
ORDER  BY FIELD(user_id,5,3,2,...,50)  LIMIT 10

回答by Ian

If you want to do arbitrary sorting on a query using values inputted by the query in MS SQL Server 2008+, it can be done by creating a table on the fly and doing a join like so (using nomenclature from OP).

如果您想使用MS SQL Server 2008+ 中查询输入的值对查询进行任意排序，可以通过动态创建表并进行像这样的连接（使用来自 OP 的命名法）来完成。

SELECT table1.name, table1.description ... 
FROM (VALUES (id1,1), (id2,2), (id3,3) ...) AS orderTbl(orderKey, orderIdx) 
LEFT JOIN table1 ON orderTbl.orderKey=table1.id
ORDER BY orderTbl.orderIdx

If you replace the VALUES statement with something else that does the same thing, but in ANSI SQL, then this should work on any SQL database.

如果您将 VALUES 语句替换为执行相同操作但在 ANSI SQL 中的其他语句，则这应该适用于任何 SQL 数据库。

Note:The second column in the created table (orderTbl.orderIdx) is necessary when querying record sets larger than 100 or so. I originally didn't have an orderIdx column, but found that with result sets larger than 100 I had to explicitly sort by that column; in SQL Server Express 2014 anyways.

注意：创建的表（orderTbl.orderIdx）中的第二列在查询大于100左右的记录集时是必需的。我最初没有 orderIdx 列，但发现结果集大于 100 时，我必须按该列显式排序；无论如何，在 SQL Server Express 2014 中。

回答by Ravi Ranjan

SELECT ORDER_NO, DELIVERY_ADDRESS 
from IFSAPP.PURCHASE_ORDER_TAB 
where ORDER_NO in ('52000077','52000079','52000167','52000297','52000204','52000409','52000126') 
ORDER BY instr('52000077,52000079,52000167,52000297,52000204,52000409,52000126',ORDER_NO)

worked really great

工作得非常好

回答by Jonathan Leffler

The IN clause describes a set of values, and sets do not have order.

IN 子句描述一组值，并且集合没有顺序。

Your solution with a join and then ordering on the display_ordercolumn is the most nearly correct solution; anything else is probably a DBMS-specific hack (or is doing some stuff with the OLAP functions in standard SQL). Certainly, the join is the most nearly portable solution (though generating the data with the display_ordervalues may be problematic). Note that you may need to select the ordering columns; that used to be a requirement in standard SQL, though I believe it was relaxed as a rule a while ago (maybe as long ago as SQL-92).

您使用连接然后在display_order列上排序的解决方案是最接近正确的解决方案；其他任何事情都可能是特定于 DBMS 的 hack（或者正在使用标准 SQL 中的 OLAP 函数做一些事情）。当然，连接是最接近可移植的解决方案（尽管用display_order值生成数据可能有问题）。请注意，您可能需要选择排序列；这曾经是标准 SQL 中的一项要求，尽管我相信它在不久前（也许早在 SQL-92 之前）作为规则放宽了。

回答by Sarthak Sawhney

Use MySQL FIND_IN_SETfunction:

使用 MySQL FIND_IN_SET函数：

  SELECT * 
    FROM table_name 
   WHERE id IN (..,..,..,..) 
ORDER BY FIND_IN_SET (coloumn_name, .., .., ..);

回答by V Patel

For Oracle, John's solution using instr() function works. Here's slightly different solution that worked - SELECT id FROM table1 WHERE id IN (1, 20, 45, 60) ORDER BY instr('1, 20, 45, 60', id)

对于 Oracle，John 使用 instr() 函数的解决方案有效。这是有效的略有不同的解决方案 - SELECT id FROM table1 WHERE id IN (1, 20, 45, 60) ORDER BY instr('1, 20, 45, 60', id)

回答by Tony

I just tried to do this is MS SQL Server where we do not have FIELD():

我只是尝试在没有 FIELD() 的 MS SQL Server 中执行此操作：

SELECT table1.id
... 
INNER JOIN
    (VALUES (10,1),(3,2),(4,3),(5,4),(7,5),(8,6),(9,7),(2,8),(6,9),(5,10)
    ) AS X(id,sortorder)
        ON X.id = table1.id
    ORDER BY X.sortorder

Note that I am allowing duplication too.

请注意，我也允许重复。