在SQL IN()子句中按值顺序排序 [英] Ordering by the order of values in a SQL IN() clause

问题描述

我想知道是否存在按照IN()子句中的值顺序进行排序的方法(可能是一种更好的方法).

问题是我有2个查询，一个查询获取所有ID，第二个查询获取所有信息.第一个创建我要第二个排序的ID的顺序.这些ID按照正确的顺序放在IN()子句中.

所以它就像(极其简化)的东西:

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

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

问题在于，第二个查询不会以将ID放入IN()子句中的顺序来返回结果.

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

还有更好的选择吗?

注意:由于第一个查询是由用户"运行的，第二个查询是在后台进程中运行的，因此无法使用子查询将第二个查询合并为一个查询.

我正在使用MySQL，但是我想让它指出其他数据库也有哪些选项可能会有用.

解决方案

使用MySQL的 FIELD() 函数:

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

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

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

将返回1

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

将返回3

如果按照相同的顺序将id粘贴到IN()子句和FIELD()函数中，这将完全满足您的要求.

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

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.

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.

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.

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.

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.

解决方案

Use MySQL's FIELD() function:

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('a', 'a', 'b', 'c')

will return 1

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

will return 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.

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