Where子句中的SQL Row_Number（）函数 [英] SQL Row_Number() function in Where Clause

问题描述

我在where子句中找到了一个用 Row_Number（）函数回答的问题。当我尝试一个查询时，出现以下错误：

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:

消息4108，级别15，状态1，第1行
窗口函数只能出现在SELECT或ORDER BY子句中。

"Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY clauses."

这是我尝试过的查询。如果有人知道如何解决此问题，请告诉我。

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

推荐答案

此问题，将您的select语句包装在CTE中，然后您可以查询CTE并在where子句中使用窗口函数的结果。

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.

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

这篇关于Where子句中的SQL Row_Number（）函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！