Server 2016中单个情况下的多列order子句

Question

我编写了一个查询，在该查询中，我有一个条件，可以按以下顺序按多个列对数据进行排序。

ORDER BY Column1 ASC, Column2 ASC, Column3 ASC

或

ORDER BY Column1 DESC, Column2 DESC, Column3 DESC

我想按表达式按条件排序，如下所示

ORDER BY 
    CASE WHEN @SortExpression = 'Id ASC' THEN Id END ASC,    
    CASE WHEN @SortExpression = 'Id DESC' THEN Id END DESC,    
    CASE WHEN @SortExpression = 'Status ASC' THEN Status END ASC,    
    CASE WHEN @SortExpression = 'Status DESC' THEN Status END DESC,   
    CASE WHEN @SortExpression = 'Country ASC' THEN Country Asc, City Asc, Zip Asc END,    
    CASE WHEN @SortExpression = 'Country DESC' THEN Country DESC, City DESC, Zip DESC END

但是上面的查询会产生语法错误。有人能帮我找出解决我问题的办法吗？

Answer 1

CASE WHEN @SortExpression = 'Country ASC' THEN Country Asc, City Asc, Zip Asc END,没有语法，您需要编写，让每个订单在一个CASE WHEN中。

如果@SortExpression只设置一个值。如果CASE WHEN不匹配，条件将返回NULL，那么let将只执行expect order列。

ORDER BY 
    CASE WHEN @SortExpression = 'Id ASC' THEN Id END ASC,    
    CASE WHEN @SortExpression = 'Id DESC' THEN Id END DESC,    
    CASE WHEN @SortExpression = 'Status ASC' THEN Status END ASC,    
    CASE WHEN @SortExpression = 'Status DESC' THEN Status END DESC,   
    CASE WHEN @SortExpression = 'Country ASC' THEN Country END Asc ,
    CASE WHEN @SortExpression = 'Country ASC' THEN City END Asc ,
    CASE WHEN @SortExpression = 'Country ASC' THEN Zip END Asc ,   
    CASE WHEN @SortExpression = 'Country DESC' THEN Country END DESC , 
    CASE WHEN @SortExpression = 'Country DESC' THEN City END DESC , 
    CASE WHEN @SortExpression = 'Country DESC' THEN Zip END DESC 

Answer 2

您的case语句需要重写下面的方式，然后编写多个列，这是不正确的(THEN Country Asc, City Asc, Zip Asc END)。

ORDER BY 
(CASE @SortExpression  when 'Id ASC'
 THEN Id End) ASC,    
(CASE @SortExpression WHEN 'Id DESC'
THEN Id END) DESC,    
(CASE @SortExpression 
WHEN 'Status ASC' 
THEN Status END ) ASC,    
(CASE @SortExpression WHEN 'Status DESC'
 THEN Status END) DESC,   
(CASE @SortExpression WHEN 'Country ASC' 
THEN Country end) Asc,
(CASE @SortExpression WHEN 'Country ASC' 
THEN City end) Asc,
(CASE @SortExpression WHEN 'Country ASC' 
THEN Zip end) Asc,         
( CASE @SortExpression  WHEN 'Country DESC'
THEN Country end ) DESC ,
( CASE @SortExpression  WHEN 'Country DESC'
THEN City end ) DESC,
( CASE @SortExpression  WHEN 'Country DESC'
THEN Zip end ) DESC