使用带有派生列的SQL排名函数

I am trying to use a SQL Server 2008 Ranking Function on a query sorted by a derived column. Here's an example

我试图在由派生列排序的查询上使用SQL Server 2008排名函数。这是一个例子

SELECT 
    table.FirstName, table.LastName, 
    CalculatedValue(table.number) As Points, 
    ROW_NUMBER() OVER (ORDER BY points) AS 'Row Number' 
FROM table 
ORDER BY points

I always get an error invalid column name "points" because the OVER function does not work with aliases, based on what I've read.

我总是得到一个错误无效的列名称“点”，因为根据我读过的内容，OVER函数不能使用别名。

Does anyone know an alternative where I can retrieve the sequential row number of a result set sorted by a derived column?

有没有人知道我可以检索由派生列排序的结果集的顺序行号的替代方法？

2 个解决方案

#1

How about using a derived table (sub query)? I think something like the following should work

如何使用派生表（子查询）？我认为以下内容应该有效

SELECT 
    ROW_NUMBER() OVER (ORDER BY sub.Points) AS 'Row Number',
    sub.FirstName,
    sub.LastName,
    sub.Points
FROM
(

    SELECT 
        table.FirstName, 
        table.LastName, 
        CalculatedValue(table.number) As Points
    FROM 
        table    
) sub
ORDER BY
    sub.Points

#2

Use a CTE to calculate your Points, then rank over the CTE.

使用CTE计算您的积分，然后在CTE上排名。

 WITH tableWithPoints AS (
   SELECT 
       table.FirstName, table.LastName, 
       CalculatedValue(table.number) As Points
   FROM table)

 SELECT FirstName, LastName, Points,
     ROW_NUMBER() OVER (ORDER BY Points) AS 'Row Number' 
 FROM 
     tableWithPoints ORDER BY Points

#1