代码之家 › 专栏 › 技术社区 › Jake Manet

MSSql列别名按ORDER BY子句排列

sql-server

Jake Manet · 技术社区 · 6 年前

我有一个复杂的查询,如:

SELECT O.OrderNo, 
**COMPLEX SELECT** as Total
FROM Order O

ORDER BY 
CASE WHEN @SortOrder = 'Ascending' THEN 1
      ELSE
           CASE 
                WHEN @SortBy = 'Total' THEN Total
           END
      END ASC,
      CASE WHEN @SortOrder = 'Descending' THEN 1
      ELSE
           CASE 
                WHEN @SortBy = 'Total' THEN Total
           END
      END DESC

但我得到了“无效的列名称总计”。我正在使用Sql Server 2016。

2 回复 | 直到 6 年前

Stefan Taseski 6 年前

您可以尝试使用CTE:

WITH [Temp] AS (
SELECT O.OrderNo, 
**COMPLEX SELECT** as Total
FROM Order O
)
SELECT * 
FROM Temp
ORDER BY 
CASE WHEN @SortOrder = 'Ascending' THEN 1
      ELSE
           CASE 
                WHEN @SortBy = 'Total' THEN Total
           END
      END ASC,
      CASE WHEN @SortOrder = 'Descending' THEN 1
      ELSE
           CASE 
                WHEN @SortBy = 'Total' THEN Total
           END
      END DESC

Chanukya Gordon Linoff 6 年前

select  * from (select *,'' total from orders)A
ORDER BY 
CASE WHEN @SortOrder = 'Ascending' THEN 1
      ELSE
           CASE 
                WHEN @SortBy = 'Total' THEN Total
           END
      END ASC,
      CASE WHEN @SortOrder = 'Descending' THEN 1
      ELSE
           CASE 
                WHEN @SortBy = 'Total' THEN Total
           END
      END DESC