Oracle提供的序号函数

Oracle提供的序号函数:
以emp表为例:
1: rownum 最简单的序号 但是在order by之前就确定值.
select rownum,t.* from emp t order by ename
  行数ROWNUMEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO1117876ADAMSCLERK77881987-5-231100　20227499ALLENSALESMAN76981981-2-20160030030367698BLAKEMANAGER78391981-5-12850　30477782CLARKMANAGER78391981-6-92450　105137902FORDANALYST75661981-12-33000　206127900JAMESCLERK76981981-12-3950　30747566JONESMANAGER78391981-4-22975　20897839KINGPRESIDENT　1981-11-175000　10957654MARTINSALESMAN76981981-9-28125014003010147934MILLERCLERK77821982-1-231300　101187788SCOTTANALYST75661987-4-193000　201217369SMITHCLERK79021980-12-17800　2013107844TURNERSALESMAN76981981-9-815000301437521WARDSALESMAN76981981-2-22125050030
2: row_number() over( order by 字段名1,字段名2,...字段名n ) 先排序再确定序号.
select row_number() over( order by ename ) as rm, t.* from emp t
   行数RMEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO117876ADAMSCLERK77881987-5-231100　20227499ALLENSALESMAN76981981-2-20160030030337698BLAKEMANAGER78391981-5-12850　30447782CLARKMANAGER78391981-6-92450　10557902FORDANALYST75661981-12-33000　20667900JAMESCLERK76981981-12-3950　30777566JONESMANAGER78391981-4-22975　20887839KINGPRESIDENT　1981-11-175000　10997654MARTINSALESMAN76981981-9-28125014003010107934MILLERCLERK77821982-1-231300　1011117788SCOTTANALYST75661987-4-193000　2012127369SMITHCLERK79021980-12-17800　2013137844TURNERSALESMAN76981981-9-8150003014147521WARDSALESMAN76981981-2-22125050030

3: 和上面的不同之处在于PARTITION分区.在每一个小分区内部取序号.
select row_number() over(PARTITION BY deptno order by sal ) as rm, t.* from emp t
   行数RMEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO117934MILLERCLERK77821982-1-231300　10227782CLARKMANAGER78391981-6-92450　10337839KINGPRESIDENT　1981-11-175000　10417369SMITHCLERK79021980-12-17800　20527876ADAMSCLERK77881987-5-231100　20637566JONESMANAGER78391981-4-22975　20747788SCOTTANALYST75661987-4-193000　20857902FORDANALYST75661981-12-33000　20917900JAMESCLERK76981981-12-3950　301027521WARDSALESMAN76981981-2-221250500301137654MARTINSALESMAN76981981-9-2812501400301247844TURNERSALESMAN76981981-9-815000301357499ALLENSALESMAN76981981-2-201600300301467698BLAKEMANAGER78391981-5-12850　30

4: rank()在这里表示针对每个部门员工的工资取序号.
select rank() over(PARTITION BY deptno order by sal ) as rnk, t.* from emp t;
   行数RNKEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO117934MILLERCLERK77821982-1-231300　10227782CLARKMANAGER78391981-6-92450　10337839KINGPRESIDENT　1981-11-175000　10417369SMITHCLERK79021980-12-17800　20527876ADAMSCLERK77881987-5-231100　20637566JONESMANAGER78391981-4-22975　20747788SCOTTANALYST75661987-4-193000　20847902FORDANALYST75661981-12-33000　20917900JAMESCLERK76981981-12-3950　301027521WARDSALESMAN76981981-2-221250500301127654MARTINSALESMAN76981981-9-2812501400301247844TURNERSALESMAN76981981-9-815000301357499ALLENSALESMAN76981981-2-201600300301467698BLAKEMANAGER78391981-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_RNKEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO117934MILLERCLERK77821982-1-231300　10227782CLARKMANAGER78391981-6-92450　10337839KINGPRESIDENT　1981-11-175000　10417369SMITHCLERK79021980-12-17800　20527876ADAMSCLERK77881987-5-231100　20637566JONESMANAGER78391981-4-22975　20747788SCOTTANALYST75661987-4-193000　20847902FORDANALYST75661981-12-33000　20917900JAMESCLERK76981981-12-3950　301027521WARDSALESMAN76981981-2-221250500301127654MARTINSALESMAN76981981-9-2812501400301237844TURNERSALESMAN76981981-9-815000301347499ALLENSALESMAN76981981-2-201600300301457698BLAKEMANAGER78391981-5-12850　30
来自： http://www.blogjava.net/terry-zj/archive/2005/11/12/19431.html