Oracle提供的序号函数

http://blog.csdn.net/shaokun305/article/details/5303344

Oracle提供的序号函数:
以emp表为例:
1: rownum 最简单的序号 但是在order by之前就确定值.
select rownum,t.* from emp t order by ename
  行数 ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 11 7876ADAMS CLERK7788 1987-5-231100 　 20
2 2 7499ALLEN SALESMAN7698 1981-2-201600 30030
3 6 7698BLAKE MANAGER7839 1981-5-12850 　 30
4 7 7782CLARK MANAGER7839 1981-6-92450 　 10
5 13 7902FORD ANALYST7566 1981-12-33000 　 20
6 12 7900JAMES CLERK7698 1981-12-3950 　 30
7 4 7566JONES MANAGER7839 1981-4-22975 　 20
8 9 7839KING PRESIDENT　 1981-11-175000 　 10
9 5 7654MARTIN SALESMAN7698 1981-9-281250 140030
10 14 7934MILLER CLERK7782 1982-1-231300 　 10
11 8 7788SCOTT ANALYST7566 1987-4-193000 　 20
12 1 7369SMITH CLERK7902 1980-12-17800 　 20
13 10 7844TURNER SALESMAN7698 1981-9-81500 0 30
14 3 7521WARD SALESMAN7698 1981-2-221250 50030
2: row_number() over( order by 字段名1,字段名2,...字段名n ) 先排序再确定序号.
select row_number() over( order by ename ) as rm, t.* from emp t
   行数 RM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 1 7876ADAMS CLERK7788 1987-5-231100 　 20
2 2 7499ALLEN SALESMAN7698 1981-2-201600 30030
3 3 7698BLAKE MANAGER7839 1981-5-12850 　 30
4 4 7782CLARK MANAGER7839 1981-6-92450 　 10
5 5 7902FORD ANALYST7566 1981-12-33000 　 20
6 6 7900JAMES CLERK7698 1981-12-3950 　 30
7 7 7566JONES MANAGER7839 1981-4-22975 　 20
8 8 7839KING PRESIDENT　 1981-11-175000 　 10
9 9 7654MARTIN SALESMAN7698 1981-9-281250 140030
10 10 7934MILLER CLERK7782 1982-1-231300 　 10
11 11 7788SCOTT ANALYST7566 1987-4-193000 　 20
12 12 7369SMITH CLERK7902 1980-12-17800 　 20
13 13 7844TURNER SALESMAN7698 1981-9-81500 0 30
14 14 7521WARD SALESMAN7698 1981-2-221250 50030


3: 和上面的不同之处在于PARTITION分区.在每一个小分区内部取序号.
select row_number() over(PARTITION BY deptno order by sal ) as rm, t.* from emp t
   行数 RM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 1 7934MILLER CLERK7782 1982-1-231300 　 10
2 2 7782CLARK MANAGER7839 1981-6-92450 　 10
3 3 7839KING PRESIDENT　 1981-11-175000 　 10
4 1 7369SMITH CLERK7902 1980-12-17800 　 20
5 2 7876ADAMS CLERK7788 1987-5-231100 　 20
6 3 7566JONES MANAGER7839 1981-4-22975 　 20
7 4 7788SCOTT ANALYST7566 1987-4-193000 　 20
8 5 7902FORD ANALYST7566 1981-12-33000 　 20
9 1 7900JAMES CLERK7698 1981-12-3950 　 30
10 2 7521WARD SALESMAN7698 1981-2-221250 50030
11 3 7654MARTIN SALESMAN7698 1981-9-281250 140030
12 4 7844TURNER SALESMAN7698 1981-9-81500 0 30
13 5 7499ALLEN SALESMAN7698 1981-2-201600 30030
14 6 7698BLAKE MANAGER7839 1981-5-12850 　 30


4: rank()在这里表示针对每个部门员工的工资取序号.
select rank() over(PARTITION BY deptno order by sal ) as rnk, t.* from emp t;
   行数 RNK EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 1 7934MILLER CLERK7782 1982-1-231300 　 10
2 2 7782CLARK MANAGER7839 1981-6-92450 　 10
3 3 7839KING PRESIDENT　 1981-11-175000 　 10
4 1 7369SMITH CLERK7902 1980-12-17800 　 20
5 2 7876ADAMS CLERK7788 1987-5-231100 　 20
6 3 7566JONES MANAGER7839 1981-4-22975 　 20
7 4 7788SCOTT ANALYST7566 1987-4-193000 　 20
8 4 7902FORD ANALYST7566 1981-12-33000 　 20
9 1 7900JAMES CLERK7698 1981-12-3950 　 30
10 2 7521WARD SALESMAN7698 1981-2-221250 50030
11 2 7654MARTIN SALESMAN7698 1981-9-281250 140030
12 4 7844TURNER SALESMAN7698 1981-9-81500 0 30
13 5 7499ALLEN SALESMAN7698 1981-2-201600 30030
14 6 7698BLAKE MANAGER7839 1981-5-12850 　 30


5: 从例子中可以看到dense_rank()和rank()的唯一区别就是:
dense_rank()中并列第二名后是第三名.
rank()中并列第二名后是第四名.
select dense_rank() over(PARTITION BY deptno order by sal ) as dense_rnk, t.* from emp t;
   行数 DENSE_RNKEMPNO ENAMEJOB MGRHIREDATE SALCOMM DEPTNO
1 1 7934MILLER CLERK7782 1982-1-231300 　 10
2 2 7782CLARK MANAGER7839 1981-6-92450 　 10
3 3 7839KING PRESIDENT　 1981-11-175000 　 10
4 1 7369SMITH CLERK7902 1980-12-17800 　 20
5 2 7876ADAMS CLERK7788 1987-5-231100 　 20
6 3 7566JONES MANAGER7839 1981-4-22975 　 20
7 4 7788SCOTT ANALYST7566 1987-4-193000 　 20
8 4 7902FORD ANALYST7566 1981-12-33000 　 20
9 1 7900JAMES CLERK7698 1981-12-3950 　 30
10 2 7521WARD SALESMAN7698 1981-2-221250 50030
11 2 7654MARTIN SALESMAN7698 1981-9-281250 140030
12 3 7844TURNER SALESMAN7698 1981-9-81500 0 30
13 4 7499ALLEN SALESMAN7698 1981-2-201600 30030
14 5 7698BLAKE MANAGER7839 1981-5-12850 　 30
来自： http://www.blogjava.net/terry-zj/archive/2005/11/12/19431.html