oracle 保持“IN”条款的顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14139931/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Keep order from 'IN' clause
提问by bAN
Is it possible to keep order from a 'IN' conditional clause?
是否可以保持“IN”条件子句的顺序？
I found this questionon SO but in his example the OP have already a sorted 'IN' clause.
我在 SO 上发现了这个问题，但在他的例子中，OP 已经有一个排序的“IN”子句。
My case is different, 'IN' clause is in random order Something like this :
我的情况不同，“IN”子句的顺序是随机的，像这样：
SELECT SomeField,OtherField
FROM TestResult
WHERE TestResult.SomeField IN (45,2,445,12,789)
I would like to retrieve results in (45,2,445,12,789) order. I'm using an Oracle database. Maybe there is an attribute in SQL I can use with the conditional clause to specify to keep order of the clause.
我想按 (45,2,445,12,789) 顺序检索结果。我正在使用 Oracle 数据库。也许 SQL 中有一个属性可以与条件子句一起使用，以指定保持子句的顺序。
采纳答案by David Aldridge
There will be no reliable ordering unless you use an ORDER BY clause ..
除非您使用 ORDER BY 子句，否则将没有可靠的排序。
SELECT SomeField,OtherField
FROM TestResult
WHERE TestResult.SomeField IN (45,2,445,12,789)
order by case TestResult.SomeField
when 45 then 1
when 2 then 2
when 445 then 3
...
end
You could split the query into 5 queries union all'd together though ...
您可以将查询拆分为 5 个查询并合并在一起...
SELECT SomeField,OtherField
FROM TestResult
WHERE TestResult.SomeField = 4
union all
SELECT SomeField,OtherField
FROM TestResult
WHERE TestResult.SomeField = 2
union all
...
I'd trust the former method more, and it would probably perform much better.
我更相信前一种方法，它的性能可能会好得多。
回答by Hamlet Hakobyan
Try this:
尝试这个：
SELECT T.SomeField,T.OtherField
FROM TestResult T
JOIN
(
SELECT 1 as Id, 45 as Val FROM dual UNION ALL
SELECT 2, 2 FROM dual UNION ALL
SELECT 3, 445 FROM dual UNION ALL
SELECT 4, 12 FROM dual UNION ALL
SELECT 5, 789 FROM dual
) I
ON T.SomeField = I.Val
ORDER BY I.Id
回答by Pero
Decode functioncomes handy in this case instead of case expressions:
在这种情况下，解码函数很方便，而不是case 表达式：
SELECT SomeField,OtherField
FROM TestResult
WHERE TestResult.SomeField IN (45,2,445,12,789)
ORDER BY DECODE(SomeField, 45,1, 2,2, 445,3, 12,4, 789,5)
Note that value,positionpairs (e.g. 445,3) are kept together for readability reasons.
请注意，出于可读性原因，value,position对（例如445,3）保持在一起。
回答by Gordon Linoff
There is an alternative that uses string functions:
还有一种使用字符串函数的替代方法：
with const as (select ',45,2,445,12,789,' as vals)
select tr.*
from TestResult tr cross join const
where instr(const.vals, ','||cast(tr.somefield as varchar(255))||',') > 0
order by instr(const.vals, ','||cast(tr.somefield as varchar(255))||',')
I offer this because you might find it easier to maintain a string of values rather than an intermediate table.
我提供这个是因为您可能会发现维护一串值比维护一个中间表更容易。
回答by Regular Joe
I was able to do this in my application using (using SQL Server 2016)
我能够在我的应用程序中使用（使用 SQL Server 2016）
select ItemID, iName
from Items
where ItemID in (13,11,12,1)
order by CHARINDEX(' ' + Convert("varchar",ItemID) + ' ',' 13 , 11 , 12 , 1 ')
I used a code-side regex to replace \b(word boundary) with a space. Something like...
我使用代码端正则表达式\b用空格替换（字边界）。就像是...
var mylist = "13,11,12,1";
var spacedlist = replace(mylist,/\b/," ");
Importantly, because I can in my scenario, I cache the result until the next time the related items are updated, so that the query is only run at item creation/modification, rather than with each item viewing, helping to minimize any performance hit.
重要的是，因为我可以在我的场景中缓存结果，直到下一次更新相关项目，以便查询仅在项目创建/修改时运行，而不是在每个项目查看时运行，有助于最大限度地减少任何性能损失。
