排列（rank()）函数。这些排列函数提供了定义一个集合（使用 PARTITION 子句），然后根据某种排序方式对这个集合内的元素进行排列的能力，下面以scott用户的emp表为例来说明rank over partition如何使用

1）查询员工薪水并连续求和

select deptno,ename,sal,

sum(sal)over(order by ename) sum1,? /*表示连续求和*/
sum(sal)over() sum2,?????????????????????????? /*相当于求和sum(sal)*/
100* round(sal/sum(sal)over(),4) "bal%"
from emp

结果如下：

??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? bal%
---------- ---------- ---------- ---------- ---------- ----------
??????? 20 ADAMS??????????? 1100?????? 1100????? 29025?????? 3.79
??????? 30 ALLEN??????????? 1600?????? 2700????? 29025?????? 5.51
??????? 30 BLAKE??????????? 2850?????? 5550????? 29025?????? 9.82
??????? 10 CLARK??????????? 2450?????? 8000????? 29025?????? 8.44
??????? 20 FORD???????????? 3000????? 11000????? 29025????? 10.34
??????? 30 JAMES???????????? 950????? 11950????? 29025?????? 3.27
??????? 20 JONES??????????? 2975????? 14925????? 29025????? 10.25
??????? 10 KING???????????? 5000????? 19925????? 29025????? 17.23
??????? 30 MARTIN?????????? 1250????? 21175????? 29025?????? 4.31
??????? 10 MILLER?????????? 1300????? 22475????? 29025?????? 4.48
??????? 20 SCOTT??????????? 3000????? 25475????? 29025????? 10.34

??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? bal%
---------- ---------- ---------- ---------- ---------- ----------
??????? 20 SMITH???????????? 800????? 26275????? 29025?????? 2.76
??????? 30 TURNER?????????? 1500????? 27775????? 29025?????? 5.17
??????? 30 WARD???????????? 1250????? 29025????? 29025?????? 4.31

2）如下：

select deptno,ename,sal,
sum(sal)over(partition by deptno order by ename) sum1,/*表示按部门号分氏，按姓名排序并连续求和*/
sum(sal)over(partition by deptno) sum2,/*表示部门分区，求和*/
sum(sal)over(partition by deptno order by sal) sum3,/*按部门分区，按薪水排序并连续求和*/
100* round(sal/sum(sal)over(),4) "bal%"
from emp

结果如下：

??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? SUM3?????? bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
??????? 10 CLARK??????????? 2450?????? 2450?????? 8750?????? 3750?????? 8.44
??????? 10 KING???????????? 5000?????? 7450?????? 8750?????? 8750????? 17.23
??????? 10 MILLER?????????? 1300?????? 8750?????? 8750?????? 1300?????? 4.48
??????? 20 ADAMS??????????? 1100?????? 1100????? 10875?????? 1900?????? 3.79
??????? 20 FORD???????????? 3000?????? 4100????? 10875????? 10875????? 10.34
??????? 20 JONES??????????? 2975?????? 7075????? 10875?????? 4875????? 10.25
??????? 20 SCOTT??????????? 3000????? 10075????? 10875????? 10875????? 10.34
??????? 20 SMITH???????????? 800????? 10875????? 10875??????? 800?????? 2.76
??????? 30 ALLEN??????????? 1600?????? 1600?????? 9400?????? 6550?????? 5.51
??????? 30 BLAKE??????????? 2850?????? 4450?????? 9400?????? 9400?????? 9.82
??????? 30 JAMES???????????? 950?????? 5400?????? 9400??????? 950?????? 3.27

??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? SUM3?????? bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
??????? 30 MARTIN?????????? 1250?????? 6650?????? 9400?????? 3450?????? 4.31
??????? 30 TURNER?????????? 1500?????? 8150?????? 9400?????? 4950?????? 5.17
??????? 30 WARD???????????? 1250?????? 9400?????? 9400?????? 3450?????? 4.31

3）如下：

select empno,deptno,sal,
sum(sal)over(partition by deptno) "deptSum",/*按部门分区，并求和*/
rank()over(partition by deptno order by sal desc nulls last)? rank, /*按部门分区，按薪水排序并计算序号*/
dense_rank()over(partition by deptno order by sal desc nulls last) d_rank,
row_number()over(partition by deptno order by sal desc nulls last) row_rank
from emp

注：

rang()涵数主要用于排序，并给出序号

dense_rank()：功能同rank()一样，区别在于，rank()对于排序并的数据给予相同序号，接下来的数据序号直接跳中跃，dense_rank()则不是，比如数据：1，2，2，4，5，6.。。。。这是rank()的形式

????????????????????????????????????????