Функция SQL Row_Number() в предложении Where

Question

Функция SQL Row_Number() в предложении Where

Я нашел один вопрос с ответом Row_Number() функция в предложении, где. Когда я попробовал один запрос, я получил следующую ошибку:

"Msg 4108, уровень 15, состояние 1, строка 1 Оконные функции могут появляться только в предложениях SELECT или ORDER BY".

Вот запрос, который я пробовал. Если кто-то знает, как решить эту проблему, пожалуйста, дайте мне знать.

SELECT employee_id 
FROM V_EMPLOYEE 
WHERE row_number() OVER ( ORDER BY employee_id ) > 0 
ORDER BY Employee_ID

122

sql sql-server t-sql analytic-functions

Источник

23 сен '09 в 16:00

9 ответов

Другие вопросы по тегам sql sql-server t-sql analytic-functions

user36297 23 сен '09 в 16:04 2009-09-23 16:04 · Answer 1 · 2009-09-23 16:04

Чтобы обойти эту проблему, поместите ваш оператор 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

user55159 23 сен '09 в 16:04 2009-09-23 16:04 · Answer 2 · 2009-09-23 16:04

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

Обратите внимание, что этот фильтр избыточен: ROW_NUMBER() начинается с 1 и всегда больше чем 0,

73

Источник

user55159 23 сен '09 в 16:04

user1134031 19 мар '12 в 05:54 2012-03-19 05:54 · Answer 3 · 2012-03-19 05:54

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

37

Источник

user1134031 19 мар '12 в 05:54

user106356 23 сен '09 в 16:05 2009-09-23 16:05 · Answer 4 · 2009-09-23 16:05

Я думаю, что вы хотите что-то вроде этого:

SELECT employee_id 
FROM  (SELECT employee_id, row_number() 
       OVER (order by employee_id) AS 'rownumber' 
       FROM V_EMPLOYEE) TableExpressionsMustHaveAnAliasForDumbReasons
WHERE rownumber > 0

27

Источник

user106356 23 сен '09 в 16:05

user2778405 14 авг '19 в 00:31 2019-08-14 00:31 · Answer 5 · 2019-08-14 00:31

Мне кажется, что все ответы, показывающие использование CTE или Sub Query, являются достаточными исправлениями для этого, но я не вижу, чтобы кто-то понял, почему у OP проблема. Причина, по которой предложенный OP не работает, связана с логическим порядком обработки запросов здесь:

ОТ

НА

ПРИСОЕДИНИТЬСЯ

ГДЕ

ГРУППА ПО

С CUBE/ROLLUP

ИМЕЕТ

ВЫБРАТЬ

ОТЛИЧИТЕЛЬНЫЙ

СОРТИРОВАТЬ ПО

ТОП

Смещение / выборка

Я считаю, что это в значительной степени способствует ответу, поскольку объясняет, почему возникают подобные проблемы. WHERE всегда обрабатывается до SELECTсоздание CTE или подзапроса, необходимого для многих функций. Вы часто будете видеть это в SQL Server.

user121544 23 сен '09 в 18:15 2009-09-23 18:15 · Answer 6 · 2009-09-23 18:15

В ответ на комментарии к ответу 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

Планы запроса были точно такими же. Я ожидаю, что во всех случаях оптимизатор запросов будет предлагать один и тот же план, по крайней мере, при простой замене CTE на встроенное представление или наоборот.

Конечно, попробуйте свои собственные запросы в своей системе, чтобы увидеть, есть ли разница.

Также, row_number() в предложении where - распространенная ошибка в ответах на переполнение стека. Logicaly row_number() недоступно, пока не будет обработано предложение select. Люди забывают об этом, и когда они отвечают, не проверяя ответ, ответ иногда бывает неправильным. (Обвинение, в котором я сам был виновен.)

user5567396 16 ноя '15 в 11:42 2015-11-16 11:42 · Answer 7 · 2015-11-16 11:42

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

7

Источник

user5567396 16 ноя '15 в 11:42

user135152 23 сен '09 в 16:29 2009-09-23 16:29 · Answer 8 · 2009-09-23 16:29

Использование 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

Использование встроенного представления / эквивалентной версии без 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

user65223 23 сен '09 в 18:25 2009-09-23 18:25 · Answer 9 · 2009-09-23 18:25

На основании ответа ОП на вопрос:

Пожалуйста, смотрите эту ссылку. Это имеет другое решение, которое выглядит работающим для человека, который задал вопрос. Я пытаюсь найти решение, подобное этому.
Разбитый на страницы запрос, использующий сортировку по различным столбцам с помощью ROW_NUMBER() OVER () в SQL Server 2005
~ Джозеф

"метод 1" похож на запрос ОП из связанного вопроса, а "метод 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

ВЫХОД:

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)

user10926078 17 янв '19 в 05:41 2019-01-17 05:41 · Answer 10 · 2019-01-17 05:41

 select salary from (
 select  Salary, ROW_NUMBER() over (order by Salary desc) rn from Employee 
 ) t where t.rn = 2

-1

Источник

user10926078 17 янв '19 в 05:41