在SQL Server中不能在ROW_NUMBER（）中使用别名？

I have to create a row_number column ordered by a grouped sum, when using sql:

当使用sql时，我必须创建一个按分组总和排序的row_number列：

select Sales.Name, SUM(Sales.Bill) as billsum, ROW_NUMBER() over (order by billsum DESC) as rn
from Sales group by Sales.Name

It reports error because row_number over cannot parse the "billsum" alias, I have to write:

它报告错误，因为row_number over无法解析“billsum”别名，我必须写：

select Sales.Name, SUM(Sales.Bill) as billsum, ROW_NUMBER() over (order by SUM(Sales.Bill) DESC) as rn
from Sales group by Sales.Name

so here I write SUM(Sales.Bill) twice, is there anyway to use the alias here?

所以在这里我写了两次SUM（Sales.Bill），反正在这里使用别名吗？

4 个解决方案

#1

The MSDN docs for the T-SQL OVER clause say:

T-SQL OVER子句的MSDN文档说：

value_expression cannot refer to expressions or aliases in the select list.

value_expression不能引用选择列表中的表达式或别名。

#2

As already stated out by other member you either have to use CTE or SubQuery.

正如其他成员已经说明的那样，您必须使用CTE或SubQuery。

Not only Row_Number() function but in tsql you can not reference alias in same query, so either you have to use one of the mentioned way above or the expression you used in your post. I hope it makes sense!! :)

不仅是Row_Number（）函数，而且在tsql中你不能在同一个查询中引用别名，所以你要么必须使用上面提到的方法之一，要么使用你在帖子中使用的表达式。我希望它有道理！ :)

#3

Possible work-arounds are to use CTE or a subquery:

可能的解决方法是使用CTE或子查询：

SELECT Name, billsum, ROW_NUMBER() OVER (ORDER BY billsum DESC) AS rn
FROM 
  ( SELECT Sales.Name, SUM(Sales.Bill) AS billsum 
    FROM Sales
    GROUP BY Sales.Name
  ) tmp

#4

-1

-- Reorder after cutting out qty = 0.
SELECT  *,ROW_NUMBER()  OVER  (partition by claimno ORDER BY itemno) as 'alias name'
from  dbo.OrderCol
where QTY <> 0

#1