row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).

　　与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

　　row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

　　rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

　　dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 ．

　　lag（arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。

看几个SQL语句:

语句一:

select row_number() over(order by sale/cnt desc) as sort, sale/cnt
from (
select -60 as sale,3 as cnt from dual union
select 24 as sale,6 as cnt from dual union
select 50 as sale,5 as cnt from dual union
select -20 as sale,2 as cnt from dual union
select 40 as sale,8 as cnt from dual);

执行结果:

????????? SORT?????? SALE/CNT
---------- ----------
???????????? 1???????????? 10
???????????? 2????????????? 5
???????????? 3????????????? 4
???????????? 4??????????? -10
???????????? 5??????????? -20

?

语句二:查询员工的工资,按部门排序

select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;

执行结果:

ENAME?????????????????????????? SAL????? SAL_ORDER
-------------------- ---------- ----------
KING?????????????????????????? 5000????????????? 1
CLARK????????????????????????? 2450????????????? 2
MILLER???????????????????????? 1300????????????? 3
SCOTT????????????????????????? 3000????????????? 1
FORD?????????????????????????? 3000????????????? 2
JONES????????????????????????? 2975????????????? 3
ADAMS????????????????????????? 1100????????????? 4
SMITH?????????????????????????? 800????????????? 5
BLAKE????????????????????????? 2850????????????? 1
ALLEN????????????????????????? 1600????????????? 2
TURNER???????????????????????? 1500????????????? 3
WARD?????????????????????????? 1250????????????? 4
MARTIN???????????????????????? 1250????????????? 5
JAMES?????????????????????????? 950????????????? 6

已选择14行。

语句三:查询每个部门的最高工资

select deptno,ename,sal from
???? (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
???????? from scott.emp) where sal_order <2;

执行结果:

?????? DEPTNO ENAME????????????????????????? SAL
---------- -------------------- ----------
?????????? 10 KING????????????????????????? 5000
?????????? 20 SCOTT???????????????????????? 3000
?????????? 30 BLAKE???????????????????????? 2850

已选择3行。

语句四：

select deptno,sal,rank() over (partition by deptno order by sal) as rank_order from scott.emp order by deptno;

执行结果:

???? DEPTNO???????? SAL RANK_ORDER
---------- ---------- ----------
???????? 10??????? 1300?????????? 1
???????? 10??????? 2450?????????? 2
???????? 10??????? 5000?????????? 3
???????? 20???????? 800?????????? 1
???????? 20??????? 1100?????????? 2
???????? 20??????? 2975?????????? 3
???????? 20??????? 3000?????????? 4
???????? 20??????? 3000?????????? 4
???????? 30???????? 950?????????? 1
???????? 30??????? 1250?????????? 2
???????? 30??????? 1250?????????? 2
???????? 30??????? 1500?????????? 4
???????? 30??????? 1600?????????? 5
???????? 30??????? 2850?????????? 6

已选择14行。

语句五：

select deptno,sal,dense_rank() over(partition by deptno order by sal) as dense_rank_order from scott.emp order by deptn;

执行结果：

???? DEPTNO???????? SAL DENSE_RANK_ORDER
---------- ---------- ----------------
???????? 10??????? 1300???????????????? 1
???????? 10??????? 2450???????????????? 2
???????? 10??????? 5000???????????????? 3
???????? 20???????? 800???????????????? 1
????????