在 where 子句中使用排名函数派生列 (SQL Server 2008)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1479831/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using ranking-function derived column in where clause (SQL Server 2008)
提问by Nick Veys
Hoping this is trivial for a SQL-Ninja... Been trying to get the following query working:
希望这对于 SQL-Ninja 来说是微不足道的......一直试图让以下查询工作：
This is for SQL Server 2008
这适用于 SQL Server 2008
SELECT
ROW_NUMBER() OVER (ORDER BY Date_Time DESC) AS RowNumber, *
FROM
(SELECT
T.A_ID, T.User_Name, T.Date_Time, T.Value,
U.ID, U.Name, U.Field1, U.Field2,
COUNT(U.ID) OVER () AS TotalRows
FROM
TeeTable as T
INNER JOIN
YouTable AS U ON T.U_ID = U.ID
WHERE
T.Value BETWEEN 222 AND 225) Filtered
WHERE
RowNumber BETWEEN 1 AND 5
The values are somewhat contrived to give a specific example, but the spirit of the query is completely preserved. The error I get from this statement is:
这些值有些人为地给出了一个特定的例子，但查询的精神完全保留了下来。我从这个声明中得到的错误是：
Invalid column name 'RowNumber'.
无效的列名“RowNumber”。
If I remove the final WHERE clause (RowNumber BETWEEN ...) it returns an expected result set (A_ID, User_Name, Date_Time etc...), with RowNumber as a column (with sensical values) in said results. Yet I cannot compare against it in the WHERE clause. I'm clearly doing something stupid but this hits my SQL limit!
如果我删除最后的 WHERE 子句（RowNumber BETWEEN ...），它会返回一个预期的结果集（A_ID、User_Name、Date_Time 等），其中 RowNumber 作为所述结果中的一列（具有合理的值）。但是我无法在 WHERE 子句中与它进行比较。我显然在做一些愚蠢的事情，但这达到了我的 SQL 限制！
I've tried re-arranging this as a CTE as well, (WITH Filtered AS ...) but the end result is the same, it appears that's just a sugar for what I'm already doing anyway.
我也尝试将其重新安排为 CTE，（带有过滤的 AS ...）但最终结果是相同的，这似乎只是我已经在做的事情的糖。
Ideas? How can I filter against the RowNumber derived column?
想法？如何过滤 RowNumber 派生列？
回答by Remus Rusanu
You must move the WHERE operator abovethe project list where RowNumber column is created. Use a derived table or a CTE:
您必须将 WHERE 运算符移到创建 RowNumber 列的项目列表上方。使用派生表或 CTE：
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (...) as RowNumber
FROM ...) As ...
WHERE RowNumber = ...
the equivalent CTE is:
等效 CTE 为：
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (...) as RowNumber
FROM ...)
SELECT * FROM cte
WHERE RowNumber = ...
回答by RBarryYoung
The Window Functions (of which ROW_NUMBER is the best know) are filled in very late in the query, well after the WHERE clause. Therefore you have to nest it too, in order to filter on it:
窗口函数（其中 ROW_NUMBER 是最了解的）在查询的后期填充，在 WHERE 子句之后。因此，您也必须嵌套它，以便对其进行过滤：
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Date_Time DESC) AS RowNumber, *
FROM
( SELECT T.A_ID, T.User_Name, T.Date_Time, T.Value,
U.ID, U.Name, U.Field1, U.Field2,
COUNT(U.ID) OVER () AS TotalRows
FROM
TeeTable as T INNER JOIN YouTable AS U
ON T.U_ID = U.ID
WHERE T.Value BETWEEN 222 AND 225
) Numbered
) Filtered
WHERE RowNumber BETWEEN 1 AND 5
You can also put them in CTE's or Views to get the same effect.
您也可以将它们放在 CTE 或视图中以获得相同的效果。
回答by Shashank Raj Chavan
The Problem with the Query comes in because of the the fault in the Logical processing order. This is the logical processing order specified in MSDN dev Network.
由于逻辑处理顺序错误，查询出现问题。这是 MSDN dev Network 中指定的逻辑处理顺序。
Logical Processing Order of the SELECT statement The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list. FROM ON JOIN WHERE GROUP BY WITH CUBE or WITH ROLLUP HAVING SELECT DISTINCT ORDER BY TOP
SELECT 语句的逻辑处理顺序 以下步骤显示了 SELECT 语句的逻辑处理顺序或绑定顺序。此顺序决定了在一个步骤中定义的对象何时可用于后续步骤中的子句。例如，如果查询处理器可以绑定（访问）在 FROM 子句中定义的表或视图，则这些对象及其列可用于所有后续步骤。相反，由于 SELECT 子句是第 8 步，因此该子句中定义的任何列别名或派生列都不能被前面的子句引用。但是，它们可以被后续子句引用，例如 ORDER BY 子句。请注意，语句的实际物理执行由查询处理器确定，并且顺序可能与此列表不同。
So introduce the ranking function as an alias in the subquery in the from section and then you will be able to set a condition on the alias in the where section.
因此，在 from 部分的子查询中引入排名函数作为别名，然后您将能够在 where 部分中对别名设置条件。
Good Luck..!!
祝你好运..！！
