问题描述
我正在尝试使用IN
子句执行SELECT
,我希望能够以与IN
列表中的元素相同的顺序返回结果.例如:
I'm trying to perform a SELECT
with an IN
clause and I would like to be able to have the results returned in the same order as the elements in my list for the IN
. For example:
SELECT * FROM orders WHERE order_no IN ('B123', 'B483', 'B100', 'B932', ...);
,我希望他们以相同的顺序回来.理想情况下,如果我能有这样的声明,那就太好了
and I would want them to come back in that same order. Ideally, it'd be great if I could have a statement like:
SELECT * FROM orders WHERE order_no IN ('B123', 'B483', 'B100', 'B932', ...)
ORDER BY ('B123', 'B483', 'B100', 'B932', ...);
我已经看到了使用 CASE
或 DECODE
关键字来定义某种自定义顺序.但是,在所有这些示例中,它们的顺序都是针对一组预定的选项.鉴于我的订购完全取决于用户输入的搜索条件,因此可能有2个选项的列表或100个要订购的列表...
I've seen examples of queries using the CASE
or DECODE
keywords to define some sort of custom ordering. But, in all those examples, their ordering was for a predetermined set of options. Whereas, my ordering is completely dependent on what my user enters for their search criteria, so there could be a list of 2 options or a list of 100 to order by...
有什么想法吗?我不知道的某些Oracle功能,或以某种方式将CASE
或DECODE
用于动态集?
Any ideas? Some Oracle feature I don't know of, or some way to use CASE
or DECODE
for a dynamic set?
推荐答案
将这些值插入到临时表中,然后将您的选择加入该表中.
Insert the values into a temporary table and join your select to that.
然后您可以在临时表列上进行自然排序.
You can then do a natural order on your temporary table column.
CREATE GLOBAL TEMPORARY TABLE sort_table (
value VARCHAR2(100),
sort_order NUMBER
) ON COMMIT DELETE ROWS;
INSERT INTO sort_table VALUES ('B123',1);
INSERT INTO sort_table VALUES ('B483',2);
... etc. ...
select * from mytable
inner join sort_table
on mytable.mycolumn = sort_table.value
order by sort_table.sort_order;
要清除临时表,只需COMMIT
.
这篇关于SQL-根据查询参数保留排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!