sql - Oracle动态DESC和ASC按顺序排列

Question

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

现在，我需要确保排序顺序也是动态的。有什么方法可以使上述查询中的排序顺序动态化？

Answer 1

如果您还想使排序顺序(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

根据您想要的排序顺序，使用包含1或-1的 l_so变量。