Where子句中的SQL Row

本文介绍了Where子句中的SQL Row_Number（）函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在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