CTE 选择"动态排序"列名称

Question

;with cte as select * from customer

通过传递参数选择 CTE 顺序

if(@orderby=1)
begin
select * from cte order by name
end
else if (@oderby=2)
begin
select * from cte order by applydate   
end
else
begin
select * from cte order by customeramount
end

以下错误无效对象 CTE 如何解决此 SQL 查询

我必须解决问题

代码是

SELECT * FROM cte ORDER BY CASE @OrderBy WHEN 0 THEN Name ELSE null END , CASE  @OrderBy WHEN 1 THEN  Code ELSE null END ,CASE @OrderBy  WHEN 2 THEN  ApplyAmountTotal ELSE null END ,CASE @OrderBy WHEN 3 THEN ApplyDate ELSE null END 

Answer 1

您可以按如下方式使用案例：

;With cte as 
(   select * from customer  )
Select * from cte 
Order by (case when @orderby=1 then [name]
when @orderby=2 then applydate
else customerAmount end )

Answer 2

我找到了答案

SELECT * FROM cte ORDER BY CASE @OrderBy WHEN 0 THEN Name ELSE null END , CASE  @OrderBy WHEN 1 THEN  Code ELSE null END ,CASE @OrderBy  WHEN 2 THEN  ApplyAmountTotal ELSE null END ,CASE @OrderBy WHEN 3 THEN ApplyDate ELSE null END 

Answer 3

name、applydate、customerAmount不是相同的数据类型， 那么你可以使用 3 个这样的CASE ...END子句

SELECT * 
FROM customer 
ORDER BY        
CASE
WHEN @oderby = 1 THEN name          
ELSE ''
END,
CASE
WHEN @oderby = 2 THEN applydate
ELSE '1900-01-01'
END,
CASE
WHEN @oderby NOT IN (1,2) THEN customerAmount
ELSE 1
END