高级sql学习——rownum—top-N分析！！！

Rownum是oracle中的一个伪列，其目的就是给查询的行标注行号。可以实现查询前n行，中间几行，最后几行的功能。

注意点：

1.rownum不可以直接在前面加上表名或别名等。

2.rownum 和where 在同一层查询中，where 条件之后使用rownum 比较，只能使用<=,<,!=,<>。不能使用>,>=,=(使用=，只能是where rownum=1才可以)。否则不返回任何数据。如果使用!=或<>,那么只是返回前n-1行。

3.当rownum 和order by 在一个语句级别中(同一层)使用的时候.看这个查询的数据是否从索引中获取(或者根据索引先得到rowid然后定位行)的，如果不是，那么就是先查询出来,每行标上rownum，然后order by 将结果重新排序，那么rownum的顺序是乱的。如果排序的数据是从索引中查询的，这样结果有序。这取决于执行计划，执行计划又和oracle优化器相关。

select rownum,t.deptno,t.ename from emp t order by t.deptno; ROWNUM DEPTNO ENAME ---------- ------ ---------- 7 10 CLARK 9 10 KING 14 10 MILLER 4 20 JONES 13 20 FORD 11 20 ADAMS 1 20 SMITH 8 20 SCOTT 3 30 WARD 10 30 TURNER 2 30 ALLEN 12 30 JAMES 6 30 BLAKE 5 30 MARTIN

建议如果需要对查询的数据排序，并且对排序数据给予rownum，使用子查询，可以保证rownum 的顺序。

 

Rownum原理：
1.执行查询操作
2.将第一行的row num置为1
3.将得到的行的row num与条件相比较，如果不匹配，则抛弃行，如果匹配，则返回行
4.oracle获取下一行，然后将rownum增1
5.返回第3步

从这个原理可以知道，select rownum,name from emp where rownum>5;不返回行，因为首先执行查询select name from emp，将第1 行的rownum标为1，然后看where条件，为false，则抛弃行，执行第2 行，还是rownum标为1，看where条件还为false，所以永远是false，rownum不改变，所有的行都被抛弃，所以没有结果。

 

oracle分页：

select t3.* from( --最外层用where条件判断 select rownum rn,t2.* from( --中间层查询加上行号 select t1.* from emp t1 order by t1.ename --底层查询排序 ) t2 ) t3 where t3.rn between 6 and 10; select t3.* from( --最外层用where条件判断 select rownum rn,t2.* from( --中间层查询加上行号 select t1.* from emp t1 order by t1.ename --底层查询排序 ) t2 where rownum<=10 ) t3 where t3.rn>5; RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----- ---------- --------- ----- ----------- --------- --------- ------ 6 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 8 7839 KING PRESIDENT 1981-11-17 5000.00 10 9 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 10 7934 MILLER CLERK 7782 1982-1-23 1300.00 10