問題描述
我想知道是否有(可能是更好的方法)按 IN() 子句中值的順序排序.
問題是我有 2 個(gè)查詢，一個(gè)獲取所有 ID，第二個(gè)獲取所有信息.第一個(gè)創(chuàng)建我希望第二個(gè)排序的 ID 的順序.ID 以正確的順序放在 IN() 子句中.
所以它會(huì)是這樣的(極其簡化):
SELECT id FROM table1 WHERE ... ORDER BY display_order, nameSELECT name, description, ... WHERE id IN ([id's from first])問題是第二個(gè)查詢返回的結(jié)果與將 ID 放入 IN() 子句中的順序不同.
我發(fā)現(xiàn)的一個(gè)解決方案是將所有 ID 放入帶有自動(dòng)遞增字段的臨時(shí)表中，然后將其加入第二個(gè)查詢中.
有更好的選擇嗎?
注意:由于第一個(gè)查詢是由用戶"運(yùn)行的，而第二個(gè)查詢是在后臺(tái)進(jìn)程中運(yùn)行的，因此無法使用子查詢將 2 個(gè)查詢合并為 1 個(gè)查詢.>
我正在使用 MySQL，但我認(rèn)為讓它記錄其他 DB 的選項(xiàng)可能會(huì)很有用.
使用 MySQL 的 FIELD() 函數(shù):
SELECT 名稱、描述、...從 ...WHERE id IN([ids, any order])ORDER BY FIELD(id, [ids in order])FIELD() 將返回與第一個(gè)參數(shù)相等的第一個(gè)參數(shù)的索引(第一個(gè)參數(shù)本身除外).
FIELD('a', 'a', 'b', 'c')
將返回 1
FIELD('a', 'c', 'b', 'a')
將返回 3
如果您將 id 以相同的順序粘貼到 IN() 子句和 FIELD() 函數(shù)中，這將完全符合您的要求.
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.
這篇關(guān)于按 SQL IN() 子句中值的順序排序的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！