问题描述
我想知道是否有(可能是更好的方法)按 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() 子句中值的顺序排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！