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

查看:45
本文介绍了按 SQL IN() 子句中值的顺序排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

所以它会是这样的(极其简化):

SELECT id FROM table1 WHERE ... ORDER BY display_order, nameSELECT name, description, ... WHERE id IN ([id's from first])

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

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

有更好的选择吗?

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

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

解决方案

使用 MySQL 的 FIELD() 函数:

SELECT 名称、描述、...从 ...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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆