函数在Where子句中

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:

我在where子句中找到了一个用Row_Number()函数回答的问题。当我尝试一个查询时，我得到了以下错误:

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

“Msg 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

8 个解决方案

#1

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

#2

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 1 and is always greater than 0.

注意，这个过滤器是冗余的:ROW_NUMBER()从1开始，并且总是大于0。

#3

Select * from 
(
    Select ROW_NUMBER() OVER ( order by Id) as 'Row_Number', * 
    from tbl_Contact_Us
) as tbl
Where tbl.Row_Number = 5

#4

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

#5

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是否会更快，我重写了查询，以使用一个表I和所有人都可用的: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.)

此外，where子句中的row_number()是在堆栈溢出时给出的答案中常见的错误。在select子句被处理之前，Logicaly row_number()不可用。人们忘记了这一点，当他们没有测试答案的时候，答案有时是错误的。(这个指控我自己也有过。)

#6

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

#7

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

使用在SQL Server 2005中使用ROW_NUMBER() /()对不同列进行排序的分页查询

~Joseph

约瑟~

"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 answer to 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)

#8

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