Where 子句中的 SQL Row_Number() 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1466963/
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
SQL Row_Number() function in Where Clause
提问by
I found one question answered with the Row_Number()function in the where clause. When I tried one query, I was getting the following error:
我发现一个问题用Row_Number()where 子句中的函数回答。当我尝试一个查询时，我收到以下错误：
"Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY clauses."
“消息 4108，级别 15，状态 1，第 1 行窗口函数只能出现在 SELECT 或 ORDER BY 子句中。”
Here is the query I tried. If somebody knows how to solve this, please let me know.
这是我试过的查询。如果有人知道如何解决这个问题，请告诉我。
SELECT employee_id
FROM V_EMPLOYEE
WHERE row_number() OVER ( ORDER BY employee_id ) > 0
ORDER BY Employee_ID
回答by Scott Ivey
To get around this issue, wrap your select statement in a CTE, and then you can query against the CTE and use the windowed function's results in the where clause.
要解决此问题，请将 select 语句包装在 CTE 中，然后您可以查询 CTE 并在 where 子句中使用窗口函数的结果。
WITH MyCte AS
(
select employee_id,
RowNum = row_number() OVER ( order by employee_id )
from V_EMPLOYEE
ORDER BY Employee_ID
)
SELECT employee_id
FROM MyCte
WHERE RowNum > 0
回答by Quassnoi
SELECT employee_id
FROM (
SELECT employee_id, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
FROM V_EMPLOYEE
) q
WHERE rn > 0
ORDER BY
Employee_ID
Note that this filter is redundant: ROW_NUMBER()starts from 1and is always greater than 0.
请注意，此过滤器是多余的：ROW_NUMBER()从 开始1并始终大于0。
回答by swa
Select * from
(
Select ROW_NUMBER() OVER ( order by Id) as 'Row_Number', *
from tbl_Contact_Us
) as tbl
Where tbl.Row_Number = 5
回答by Matthew Jones
I think you want something like this:
我想你想要这样的东西：
SELECT employee_id
FROM (SELECT employee_id, row_number()
OVER (order by employee_id) AS 'rownumber'
FROM V_EMPLOYEE) TableExpressionsMustHaveAnAliasForDumbReasons
WHERE rownumber > 0
回答by Shannon Severance
In response to comments on rexem's answer, with respect to whether a an inline view or CTE would be faster I recast the queries to use a table I, and everyone, had available: sys.objects.
为了回应对 rexem 答案的评论，关于内联视图或 CTE 是否更快，我重新查询以使用我和每个人都可用的表：sys.objects。
WITH object_rows AS (
SELECT object_id,
ROW_NUMBER() OVER ( ORDER BY object_id) RN
FROM sys.objects)
SELECT object_id
FROM object_rows
WHERE RN > 1
SELECT object_id
FROM (SELECT object_id,
ROW_NUMBER() OVER ( ORDER BY object_id) RN
FROM sys.objects) T
WHERE RN > 1
The query plans produced were exactly the same. I would expect in all cases, the query optimizer would come up with the same plan, at least in simple replacement of CTE with inline view or vice versa.
生成的查询计划完全相同。我希望在所有情况下，查询优化器都会提出相同的计划，至少用内联视图简单替换 CTE，反之亦然。
Of course, try your own queries on your own system to see if there is a difference.
当然，在您自己的系统上尝试您自己的查询，看看是否有区别。
Also, row_number()in the where clause is a common error in answers given on Stack Overflow. Logicaly row_number()is not available until the select clause is processed. People forget that and when they answer without testing the answer, the answer is sometimes wrong. (A charge I have myself been guilty of.)
此外，row_number()在 where 子句中，Stack Overflow 上给出的答案是一个常见错误。row_number()在处理 select 子句之前，Logically不可用。人们忘记了这一点，当他们在没有测试答案的情况下回答时，答案有时是错误的。（我自己也犯了罪。）
回答by OMG Ponies
Using CTE (SQL Server 2005+):
使用 CTE（SQL Server 2005+）：
WITH employee_rows AS (
SELECT t.employee_id,
ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
FROM V_EMPLOYEE t)
SELECT er.employee_id
FROM employee_rows er
WHERE er.rownum > 1
Using Inline view/Non-CTE Equivalent Alternative:
使用内联视图/非 CTE 等效替代方案：
SELECT er.employee_id
FROM (SELECT t.employee_id,
ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
FROM V_EMPLOYEE t) er
WHERE er.rownum > 1
回答by Jamie Marshall
I feel like all the answers showing use of a CTE or Sub Query are sufficient fixes for this, but I don't see anyone getting to the heart of why OP has a problem. The reason why what OP suggested doesn't work is due to logical query processing order here:
我觉得所有显示使用 CTE 或子查询的答案都足以解决此问题，但我没有看到任何人深入了解 OP 出现问题的原因。OP建议不起作用的原因是由于此处的逻辑查询处理顺序：
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE/ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
- OFFSET/FETCH
- 从
- 在
- 加入
- 在哪里
- 通过...分组
- 带立方体/汇总
- 有
- 选择
- 清楚的
- 订购者
- 最佳
- 偏移/取回
I believe this contributes to the answer greatly, because it explains why issues like this one occur. WHEREis always processed before SELECTmaking a CTE or Sub Query necessary for many functions. You will see this a lot in SQL Server.
我相信这对答案有很大的帮助，因为它解释了为什么会出现这样的问题。WHERE总是在SELECT使 CTE 或子查询成为许多功能所必需的之前处理。您将在 SQL Server 中看到很多。
回答by KM.
based on OP's answer to question:
基于 OP 对问题的回答：
Please see this link. Its having a different solution, which looks working for the person who asked the question. I'm trying to figure out a solution like this.
Paginated query using sorting on different columns using ROW_NUMBER() OVER () in SQL Server 2005
~Joseph
请看这个链接。它有一个不同的解决方案，看起来对提出问题的人有用。我试图找出这样的解决方案。
在 SQL Server 2005 中使用 ROW_NUMBER() OVER () 对不同列进行排序的分页查询
~约瑟夫
"method 1" is like the OP's query from the linked question, and "method 2" is like the query from the selected answer. You had to look at the code linked in this answerto see what was really going on, since the code in the selected answer was modified to make it work. Try this:
“方法 1”就像来自链接问题的 OP 查询，而“方法 2”就像来自所选答案的查询。您必须查看此答案中链接的代码以了解实际情况，因为所选答案中的代码已被修改以使其正常工作。尝试这个：
DECLARE @YourTable table (RowID int not null primary key identity, Value1 int, Value2 int, value3 int)
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1,1,1)
INSERT INTO @YourTable VALUES (1,1,2)
INSERT INTO @YourTable VALUES (1,1,3)
INSERT INTO @YourTable VALUES (1,2,1)
INSERT INTO @YourTable VALUES (1,2,2)
INSERT INTO @YourTable VALUES (1,2,3)
INSERT INTO @YourTable VALUES (1,3,1)
INSERT INTO @YourTable VALUES (1,3,2)
INSERT INTO @YourTable VALUES (1,3,3)
INSERT INTO @YourTable VALUES (2,1,1)
INSERT INTO @YourTable VALUES (2,1,2)
INSERT INTO @YourTable VALUES (2,1,3)
INSERT INTO @YourTable VALUES (2,2,1)
INSERT INTO @YourTable VALUES (2,2,2)
INSERT INTO @YourTable VALUES (2,2,3)
INSERT INTO @YourTable VALUES (2,3,1)
INSERT INTO @YourTable VALUES (2,3,2)
INSERT INTO @YourTable VALUES (2,3,3)
INSERT INTO @YourTable VALUES (3,1,1)
INSERT INTO @YourTable VALUES (3,1,2)
INSERT INTO @YourTable VALUES (3,1,3)
INSERT INTO @YourTable VALUES (3,2,1)
INSERT INTO @YourTable VALUES (3,2,2)
INSERT INTO @YourTable VALUES (3,2,3)
INSERT INTO @YourTable VALUES (3,3,1)
INSERT INTO @YourTable VALUES (3,3,2)
INSERT INTO @YourTable VALUES (3,3,3)
SET NOCOUNT OFF
DECLARE @PageNumber int
DECLARE @PageSize int
DECLARE @SortBy int
SET @PageNumber=3
SET @PageSize=5
SET @SortBy=1
--SELECT * FROM @YourTable
--Method 1
;WITH PaginatedYourTable AS (
SELECT
RowID,Value1,Value2,Value3
,CASE @SortBy
WHEN 1 THEN ROW_NUMBER() OVER (ORDER BY Value1 ASC)
WHEN 2 THEN ROW_NUMBER() OVER (ORDER BY Value2 ASC)
WHEN 3 THEN ROW_NUMBER() OVER (ORDER BY Value3 ASC)
WHEN -1 THEN ROW_NUMBER() OVER (ORDER BY Value1 DESC)
WHEN -2 THEN ROW_NUMBER() OVER (ORDER BY Value2 DESC)
WHEN -3 THEN ROW_NUMBER() OVER (ORDER BY Value3 DESC)
END AS RowNumber
FROM @YourTable
--WHERE
)
SELECT
RowID,Value1,Value2,Value3,RowNumber
,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
FROM PaginatedYourTable
WHERE RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
ORDER BY RowNumber
--------------------------------------------
--Method 2
;WITH PaginatedYourTable AS (
SELECT
RowID,Value1,Value2,Value3
,ROW_NUMBER() OVER
(
ORDER BY
CASE @SortBy
WHEN 1 THEN Value1
WHEN 2 THEN Value2
WHEN 3 THEN Value3
END ASC
,CASE @SortBy
WHEN -1 THEN Value1
WHEN -2 THEN Value2
WHEN -3 THEN Value3
END DESC
) RowNumber
FROM @YourTable
--WHERE more conditions here
)
SELECT
RowID,Value1,Value2,Value3,RowNumber
,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
FROM PaginatedYourTable
WHERE
RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
--AND more conditions here
ORDER BY
CASE @SortBy
WHEN 1 THEN Value1
WHEN 2 THEN Value2
WHEN 3 THEN Value3
END ASC
,CASE @SortBy
WHEN -1 THEN Value1
WHEN -2 THEN Value2
WHEN -3 THEN Value3
END DESC
OUTPUT:
输出：
RowID Value1 Value2 Value3 RowNumber PageNumber PageSize SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10 2 1 1 10 3 5 1
11 2 1 2 11 3 5 1
12 2 1 3 12 3 5 1
13 2 2 1 13 3 5 1
14 2 2 2 14 3 5 1
(5 row(s) affected
RowID Value1 Value2 Value3 RowNumber PageNumber PageSize SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10 2 1 1 10 3 5 1
11 2 1 2 11 3 5 1
12 2 1 3 12 3 5 1
13 2 2 1 13 3 5 1
14 2 2 2 14 3 5 1
(5 row(s) affected)
回答by sumit
WITH MyCte AS
(
select
employee_id,
RowNum = row_number() OVER (order by employee_id)
from V_EMPLOYEE
)
SELECT employee_id
FROM MyCte
WHERE RowNum > 0
ORDER BY employee_id
回答by Aziz Khan
select salary from (
select Salary, ROW_NUMBER() over (order by Salary desc) rn from Employee
) t where t.rn = 2
