SQL-根据查询参数保留排序 [英] SQL - retain ordering based on the query params

问题描述

我正在尝试使用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-根据查询参数保留排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！