Where 절의 SQL Row_Number() 함수

Where 절의 SQL Row_Number() 함수

나는 한 질문이 답이라는 것을 발견했습니다.Row_Number()where 절의 함수입니다.하나의 쿼리를 시도했을 때 다음 오류가 발생했습니다.

"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

이 문제를 해결하려면 선택 문을 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

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.

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

당신은 다음과 같은 것을 원하는 것 같습니다.

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

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

생성된 쿼리 계획은 정확히 동일합니다.저는 모든 경우에 쿼리 최적화 프로그램이 최소한 CTE를 인라인 뷰로 단순 교체하거나 그 반대의 경우에도 동일한 계획을 제시할 것으로 예상합니다.

물론 자신의 시스템에서 직접 쿼리를 사용하여 차이점이 있는지 확인합니다.

또한.row_number()in where 절은 스택 오버플로에 지정된 응답에서 일반적인 오류입니다.논리학row_number()select 절이 처리될 때까지 사용할 수 없습니다.사람들은 그것을 잊어버리고 답을 테스트하지 않고 대답할 때 가끔 답이 틀릴 수 있습니다. (제 자신이 저지른 죄목입니다.)

CTE 또는 하위 쿼리를 사용하는 것을 보여주는 모든 답변이 이 문제에 대한 충분한 해결책이라고 생각하지만 OP에 문제가 있는 이유에 대해 핵심을 파악하는 사람은 아무도 없습니다.OP가 제안한 것이 작동하지 않는 이유는 논리적 쿼리 처리 순서 때문입니다.

1. 부터
2. ON
3. 합류하다
4. 어디에
5. 그룹화 기준
6. 큐브/롤업 포함
7. 하고 있다
8. 선택한다.
9. 분명한
10. 주문 기준
11. 정상
12. 오프셋/페치

저는 이것이 왜 이런 문제가 발생하는지를 설명해주기 때문에 답변에 크게 기여한다고 생각합니다. WHERE항상 다음 이전에 처리됩니다.SELECT많은 기능에 필요한 CTE 또는 하위 쿼리를 만듭니다.SQL Server에서 많이 볼 수 있습니다.

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

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

질문에 대한 OP의 답변을 기반으로 합니다.

이 링크를 참조하십시오.그것은 질문을 한 사람에게 효과가 있는 것처럼 보이는 다른 해결책을 가지고 있습니다.저는 이런 해결책을 찾고 있습니다.

SQL Server 2005에서 ROW_NUMBER() OVER()를 사용하여 서로 다른 열의 정렬을 사용하여 페이지로 표시된 쿼리

~요셉

"method 1"은 연결된 질문의 OP 쿼리와 같고, "method 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)

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

언급URL : https://stackoverflow.com/questions/1466963/sql-row-number-function-in-where-clause