oracle row_number用法解释

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).
与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．
DEPTNO ENAME SAL LAG_
---------- -------------------- ---------- --------------------
10 CLARK 2450
(select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
from scott.emp) where sal_order <2;
执行结果:
语句二:查询员工的工资,按部门排序
select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;
执行结果:
ENAME SAL SAL_ORDER
看几个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
MARTIN 1250 5
JAMES 950 6
已选择14行。
语句三:查询每个部门的最高工资
select deptno,ename,sal from
第一步：从根节点开始；
第二步：访问该节点；
第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
第四步：若该节点为根节点，则访问完毕，否则执行第五步；
第五步：返回到该节点的父节点，并执行第三步骤。
总之：扫描整个树结构的过程也即是中序遍历树的过程。
20 3000 4
20 3000 4
30 950 1
30 1250 2
执行结果：
DEPTNO SAL DENSE_RANK_ORDER
---------- ---------- ----------------
10 1300 1
10 2450 2
row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 ．
DEPTNO ENAME SAL
---------- -------------------- ----------
10 KING 5000
20 SCOTT 3000
lag（arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。
简单介绍如下：
早扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：
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
20 800 1
20 1100 2
20 2975 3
20 3000 4
20 3000 4
SCOTT 3000 1
FORD 3000 2
JONES 2975 3
30 TURNER 1500 MARTIN
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;
已选择14行。
语句六：
select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) as lag_ from scott.emp order by deptno;
执行结果：
执行结果:
DEPTNO SAL RANK_ORDER
---------- ---------- ----------
10 1300 1
10 2450 2
10 5000 3
20 JONES 2975 FORD
20 SCOTT 3000 JONES
20 SMITH 800 SCOTT
-------------------- ---------- ----------
KING 5000 1
CLARK 2450 2
MILLER 1300 3
30 1250 2
30 1500 3
30 1600 4
30 2850 5
ADAMS 1100 4
SMITH 800 5
BLAKE 2850 1
30 BLAKE 2850
已选择3行。
语句四：
select deptno,sal,rank() over (partition by deptno order by sal) as rank_order from scott.emp order by deptno;
30 ALLEN 1600
30 BLAKE 2850 ALLEN
30 JAMES 950 BLAKE
30 MARTIN 1250 JAMES
30 950 1
30 1250 2
30 1250 2
30 1500 4
30 1600 5
10 5000 3
20 800 1
20 1100 2
20 2975 3
ALLEN 1600 2
TURNER 1500 3
WARD 1250 4
10 KING 5000 CLARK
10 MILLER பைடு நூலகம் 1300 KING
20 ADAMS 1100
20 FORD 3000 ADAMS