oracle 如何告诉oracle按照从java传入的特定排序顺序进行排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10813767/
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
How to tell oracle to sort by a specific sort order passed in from java?
提问by goe
Here's what I need to be able to do.
这就是我需要做的事情。
I have a List in java which I can convert to comma separate string of IDs like so "3,4,5,6,1,2"
我在java中有一个列表，我可以将其转换为逗号分隔的ID字符串，例如“3,4,5,6,1,2”
I wonder if there's way to pass that string to oracle and have sql code sort based on that string's sort order?
我想知道是否有办法将该字符串传递给 oracle 并根据该字符串的排序顺序对 sql 代码进行排序？
So this query:
所以这个查询：
select t.id
from t_test t
Would result in this order
会导致这个顺序
ID
3
4
5
6
1
2
回答by Pablo
If you can modify the query in java, you could do something like this:
如果您可以在 Java 中修改查询，则可以执行以下操作：
SELECT t.id
FROM t_test t
ORDER BY DECODE(t.id, 3, 'A', 'B') ASC,
DECODE(t.id, 4, 'A', 'B') ASC,
DECODE(t.id, 5, 'A', 'B') ASC,
DECODE(t.id, 6, 'A', 'B') ASC,
DECODE(t.id, 1, 'A', 'B') ASC,
DECODE(t.id, 2, 'A', 'B') ASC;
You have to put a decode in the order by clause for each element in the list. The second parameter in each decode is one element of the list.
您必须在 order by 子句中为列表中的每个元素放置一个解码。每个解码中的第二个参数是列表的一个元素。
回答by a_horse_with_no_name
Something like this:
像这样的东西：
with ordered_ids as (
select to_number(regexp_substr ('3,4,5,6,1,2','[^,]+',1,level)) as id, level as sort_order
from dual
connect by regexp_substr ('3,4,5,6,1,2','[^,]+',1,level) is not null
)
select t.id
from t_test t
join ordered_ids oi on oi.id = t.id
order by oi.sort_order;
You can probably make the literal '3,4,5,6,1,2'a parameter for a PreparedStatement but I haven't tested that.
您可能可以将文字'3,4,5,6,1,2'作为 PreparedStatement 的参数，但我还没有测试过。
回答by hage
I don't think it's possible. You can only use ascending or descending order in queries. But what you can do is to use a prepared statement like this select * from t_test t where t.id = ?and run this for each ID in your list and add the result to a result list.
我不认为这是可能的。您只能在查询中使用升序或降序。但是您可以做的是使用这样的准备好的语句select * from t_test t where t.id = ?并为列表中的每个 ID 运行此语句并将结果添加到结果列表中。
You could also try to do this with a stored procedure whose parameter would be the list of IDs
您也可以尝试使用参数为 ID 列表的存储过程来执行此操作
EDIT
编辑
Another idea is to use IN operator on small chunks of your ID-list (maybe 10 in each). As this will return results in unspecified order, you could write a custom comparator or another class that brings this list into your specified order. Then you can concatenate all sub-lists into one result list. For a list with 100 entries and batch size 10 you would only need 10 DB queries + some time for reordering
另一个想法是在您的 ID 列表的小块上使用 IN 运算符（每个可能有 10 个）。由于这将以未指定的顺序返回结果，您可以编写一个自定义比较器或另一个类，将这个列表带入您指定的顺序。然后您可以将所有子列表连接成一个结果列表。对于包含 100 个条目和批量大小为 10 的列表，您只需要 10 个数据库查询 + 一些重新排序的时间
回答by Pramod Kumar
select t1 from (select t.id t1 from t_test t order by t.id asc);
回答by Pramod Kumar
In hibernate u can do -
在休眠状态下，您可以这样做-
public String getResult(String sortOrder){
SQLQuery query = getSession().createSQLQuery("select t from ( select t.id t from t_test t order by t.id=:sortOrder").addScalar("name", Hibernate.STRING);
query.setString("sortOrder", sortOrder);
return (String)query.uniqueResult();
}
