Oracle 动态 DESC 和 ASC 按顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4956120/
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
Oracle dynamic DESC and ASC in order by
提问by Aseem Gautam
Order by is dynamic but the sort order is static.
Order by 是动态的，但排序顺序是静态的。
SELECT ...
Order By CASE WHEN InputParam = 'PRICE' THEN OFFER_PRICE END DESC,
CASE WHEN InputParam = 'ENDING SOON' THEN EXPIRY_DATE END DESC,
CASE WHEN InputParam = 'DISCOUNT' THEN DISC_PERCENTAGE END DESC,
CASE WHEN InputParam = 'SAVING' THEN SAVING END DESC
Now I need to make sure that the sort order is also dynamic. Is there some way to make sort order dynamic in the above query?
现在我需要确保排序顺序也是动态的。有没有办法使上述查询中的排序顺序动态化？
回答by Vincent Malgrat
If you also want to make the sort order (ASC/DESC) dynamic, you could do the following:
如果您还想使排序顺序 (ASC/DESC) 动态化，您可以执行以下操作：
SELECT ...
Order By CASE WHEN InputParam = 'PRICE' THEN l_so * OFFER_PRICE END,
CASE WHEN InputParam = 'ENDING SOON'
THEN l_so * (SYSDATE - EXPIRY_DATE) END,
CASE WHEN InputParam = 'DISCOUNT' THEN l_so * DISC_PERCENTAGE END,
CASE WHEN InputParam = 'SAVING' THEN l_so * SAVING END
with a variable l_sothat contains 1 or -1 depending upon which sort order you want.
使用l_so包含 1 或 -1的变量，具体取决于您想要的排序顺序。
回答by Yale Madden
This works for me:
这对我有用：
order by
case when :dir_param = 'ASC' then
case :col_param
when 'col_1_identifier' then col_1_name
when 'col_2_identifier' then col_2_name
...
end
end,
case when :dir_param = 'DSC' then
case :col_param
when 'col_1_identifier' then col_1_name
when 'col_2_identifier' then col_2_name
...
end
end desc
or
或者
order by
case when :dir_param = 'ASC' and :col_param = 'col_1_identifier' then col_1_name end,
case when :dir_param = 'DSC' and :col_param = 'col_1_identifier' then col_1_name end desc,
case when :dir_param = 'ASC' and :col_param = 'col_2_identifier' then col_2_name end,
case when :dir_param = 'DSC' and :col_param = 'col_2_identifier' then col_2_name end desc
replace literals, variable and column names with those specific to your situation. Oracle seemed to be very picky about the placement of the desc sort direction qualifier.
将文字、变量和列名替换为特定于您的情况的名称。Oracle 似乎对 desc 排序方向限定符的放置非常挑剔。
