ORACLE三层子查询

以Oracle11G数据库中scott用户的emp雇员表为例

 

第一层查询

SELECT EMPNO, ENAME
FROM EMP 
ORDER BY EMPNO;

结果

第二层查询

SELECT ROWNUM, EMPNO, ENAME
FROM (SELECT EMPNO, ENAME
     FROM EMP 
     ORDER BY EMPNO)
WHERE ROWNUM <= 10;

结果

第三层查询

SELECT EMPNO, ENAME
FROM (SELECT ROWNUM RN, EMPNO, ENAME
     FROM (SELECT EMPNO, ENAME
          FROM EMP 
          ORDER BY EMPNO)
     WHERE ROWNUM <= 10)
WHERE RN > 5;

 结果