문제 설명

SQL #QUERY #ROWNUM #ORACLE (#SQL #QUERY #ROWNUM #ORACLE)

TABLE 직원 중 3번째 급여를 선택하고 싶습니다. 아래에서 볼 수 있는 것처럼 QUERY를 작성했지만 불행히도 0개의 레코드를 제공합니다. 누군가 이 주제에 대해 저를 도울 수 있습니까? 저는 Oracle DBMS를 사용하고 있습니다. 다음은 제 데이터베이스의 예입니다. SQL Fiddle

SELECT *
FROM 
  (SELECT ROWNUM, salary 
  FROM 
    (SELECT DISTINCT salary 
    FROM employees
    ORDER BY salary desc)
  )
WHERE ROWNUM = 3;

참조 솔루션

방법 1:

Seems to be Oracle (ROWNUM and no alias for the Derived Table). ROWNUM is calculated for the resulting rows, thus you can never filter for any ROWNUM greater than 1.

You need a Standard SQL ROW_NUMBER:

SELECT *
FROM
 ( SELECT salary
      ,row_number() over (ORDER BY salary desc) as rn 
   FROM employees
   GROUP BY salary
 ) dt
WHERE rn = 3;

The GROUP BY is equivalent to DISTINCT, but processed before the ROW_NUMBER while DISTINCT is processed after.

Edit:

If you want to use ROWNUM you must alias it:

SELECT *
FROM 
  (SELECT ROWNUM as rn, salary 
  FROM 
    (SELECT DISTINCT salary 
    FROM employees
    ORDER BY salary desc)
  )
WHERE rn = 3;

방법 2:

In standard SQL, most databases, and Oracle 12C+, you can instead use:

SELECT DISTINCT salary 
FROM employees
ORDER BY salary desc
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;

(by Bro、dnoeth、Gordon Linoff)

참조 문서

1. #SQL #QUERY #ROWNUM #ORACLE (CC BY‑SA 2.5/3.0/4.0)