oracle SQL - 根据查询参数保留排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5886457/
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
SQL - retain ordering based on the query params
提问by kafuchau
I'm trying to perform a SELECTwith an INclause 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:
我正在尝试执行SELECTwithIN子句，并且我希望能够以与IN. 例如：
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', ...);
I've seen examples of queries using the CASEor DECODEkeywords 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...
我见过使用CASEorDECODE关键字定义某种自定义排序的查询示例。但是，在所有这些示例中，它们的排序是针对一组预先确定的选项。然而，我的排序完全取决于我的用户为他们的搜索条件输入的内容，因此可能有 2 个选项的列表或 100 个的列表可供排序...
Any ideas? Some Oracle feature I don't know of, or some way to use CASEor DECODEfor a dynamic set?
有任何想法吗？一些我不知道的 Oracle 功能，或者某种使用方式CASE或DECODE用于动态集的方式？
采纳答案by jenson-button-event
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;
To clear the temporary table, just COMMIT.
要清除临时表，只需COMMIT.
回答by user3278731
you can try it will work fine. Check below sql:-
你可以试试它会工作得很好。检查下面的sql：-
SELECT * FROM orders WHERE order_no IN ('B123', 'B483', 'B100', 'B932')
ORDER BY DECODE(order_no,'B123','1','B483','2','B100','3','B932','4');
回答by a_horse_with_no_name
I don't know if there is an elegant (or short) solution for this.
我不知道是否有一个优雅（或简短）的解决方案。
If you can build the query dynamically, the following should work:
如果您可以动态构建查询，则应执行以下操作：
WITH numbers AS (
SELECT 1 as sort_order, 'B123' as order_no FROM DUAL
union all
SELECT 2 as sort_order, 'B483' as order_no FROM DUAL
union all
SELECT 2 as sort_order, 'B100' as order_no FROM DUAL
union all
SELECT 2 as sort_order, 'B932' as order_no FROM DUAL
)
SELECT orders.*
FROM numbers
LEFT JOIN orders ON orders.ord_no = numbers.ord_no
ORDER BY numbers.sort_order
回答by josephj1989
You can concatenate your variables and order by instr on it like below. I e cannot vouch for the efficiency of this - but must be Okey.Your front end will obviously have to do a bit more work.But constructing a query like this can be open to sql Injection.
您可以按如下方式连接变量并按指令排序。我不能保证它的效率 - 但必须是 Okey。你的前端显然需要做更多的工作。但是构造这样的查询可以对 sql 注入开放。
SELECT * FROM orders WHERE order_no IN ('B123', 'B483', 'B100', 'B932', ...)
ORDER BY
instr ('@B123@'|| '@B483@'||'@B100@'||'@B932@'||... ,'@'|| order_no||'@')
回答by Thilo
If you want to go with DECODE to assign a numerical sort order:
如果要使用 DECODE 分配数字排序顺序：
SELECT ID FROM tbl WHERE ID IN (2,3,1)
ORDER BY DECODE(ID, 2, 1, 3, 2, 3)
回答by Jean-Christophe Blanchard
I made an answer here in a more recent question
我在最近的一个问题中给出了答案
My answer use a pipe row function so it doesn't need to use a temp table like the accepted answer here.
我的答案使用管道行函数，因此它不需要使用像此处接受的答案那样的临时表。
回答by itsols
I'm not a Python guy, but here's how I'd do it in PHP and I hope you get the idea.
我不是一个 Python 人，但这里是我如何在 PHP 中做到这一点，我希望你能明白。
Build a string like this:
$str = "('B123', 'B483', 'B100', 'B932', ...)";
For the above, you can use a for loop or something to build a really long string.
Insert the string in the query like this:
像这样构建一个字符串：
$str = "('B123', 'B483', 'B100', 'B932', ...)";
对于上述情况，您可以使用 for 循环或其他东西来构建一个非常长的字符串。
在查询中插入字符串，如下所示：
$MyQuery = "SELECT * FROM orders WHERE order_no IN $str ORDER BY $str";
$MyQuery = "SELECT * FROM orders WHERE order_no IN $str ORDER BY $str";
Like I said, this is a PHP example, but I believe you get the idea.
就像我说的，这是一个 PHP 示例，但我相信您明白了。
