Oracle动态DESC和ASC按顺序排列 [英] Oracle dynamic DESC and ASC in order by

问题描述

排序依据是动态的，但排序顺序是静态的.

Order by is dynamic but the sort order is static.

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?

推荐答案

如果您还想使排序顺序(ASC/DESC)动态，则可以执行以下操作:

If you also want to make the sort order (ASC/DESC) dynamic, you could do the following:

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

带有变量l_so的变量，该变量包含1或-1，具体取决于所需的排序顺序.

with a variable l_so that contains 1 or -1 depending upon which sort order you want.

这篇关于Oracle动态DESC和ASC按顺序排列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！