rownum是对符合条件结果的序列号。总是从1开始排起，所以选出的结果中不能没有1而有其他大于1的值。
也可以理解为rownum是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum为1，
第2条为2。如果使用>，>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum
为1，则被删除，接着取下条，可是rownum依旧为1，有被删除，便没有了数据。
case1:
SQL> select * from emp where rownum between 2 and 5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
case2:
SQL> select * from emp where rownum<>3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
case3:
SQL> select * from emp where rownum>0;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
case4:
SQL> select * from emp where rownum between 1 and 3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
3.rownum和row_numbe() over:
使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而row_number()在包含排序从句后是先排序再计算行号码
case:
order by:
SQL> select a.*,rownum from emp a order by a.ename;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROWNUM
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 11
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 2
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 6
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 13
7900 JAMES CLERK 7698 1981/12/3 950.00 30 12
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 4
7839 KING PRESIDENT 1981/11/17 5000.00 10 9
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 5
7934 MILLER CLERK 7782 1982/1/23 1300.00 10 14
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 8
7369 SMITH CLERK 7902 1980/12/17 800.00 20 1
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 10
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 3
14 rows selected
row_number() over(order by )
SQL> select a.*,row_number() over(order by a.ename) from emp a;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROW_NUMBER()OVER(ORDERBYA.ENAM
----- ---------- --------- ----- ----------- --------- --------- ------ ------------------------------
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 1
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 2
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 3
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 4
7902 FORD ANALYST 7566 1981/12/3 3000.00