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
现在我需要确保排序顺序也是动态的.有没有办法在上面的查询中使排序顺序动态化？
解决方法
如果您还想使排序顺序(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,具体取决于您想要的排序顺序.
