SQL 动态 ASC 和 DESC [英] SQL Dynamic ASC and DESC

问题描述

我有以下 SQL 语句，其中 order by 子句是动态传递的.如何将按升序和降序排序"动态传递给 SQL?

I have the following SQL statement where order by clause is passed dynamically . How can I pass 'order by asc and desc' dynamically to SQL?

SELECT table1.prod_id,table2.prod_name from table1 left outer join table2
              ON table1.prod1 = table2.prod_id 
              ORDER BY CASE WHEN :odb = 1 THEN prod_id END

我想将order by asc or desc 动态传递给上面的SQL，

I would like pass order by asc or desc to above SQL dynamically,

我该怎么做?

推荐答案

您可以通过操作数字或数据值来实现类似 @TonyAndrews 的解决方案.对于 VARCHAR2，动态 SQL 的替代方案可能是使用两个表达式:

You can do solutions like @TonyAndrews by manipulating numeric or data values. For VARCHAR2 an alternative to dynamic SQL could be to have two expressions:

order by
   case when :sorting='ASC' then col1 end ASC,
   case when :sorting='DESC' then col1 end DESC

当 :sorting 的值为 'ASC' 时，ORDER BY 的结果就像:

When :sorting has the value 'ASC' the result of that ORDER BY becomes like if it had been:

order by
   col1 ASC,
   NULL DESC

当 :sorting 的值为 'DESC' 时，ORDER BY 的结果就像:

When :sorting has the value 'DESC' the result of that ORDER BY becomes like if it had been:

order by
   NULL ASC,
   col1 DESC

这种方法的一个缺点是优化器可以跳过 SORT 操作的那些情况，因为涉及的索引使数据已经按预期排序，当使用这样的 CASE 方法时不会发生这种情况.无论如何，这将强制执行排序操作.

One downside to this method is that those cases where the optimizer can skip a SORT operation because there is an index involved that makes the data already sorted like desired, that will not happen when using the CASE method like this. This will mandate a sorting operation no matter what.

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