row_number用法解释

而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).

　　与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

20 1100 2

20 2975 3

20 3000 4

20 3000 4

30 950 1

30 1250 2

30 1250 2

30 1500 3

30 1600 4

30 2850 5

已选择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 ENAME SAL LAG_

---------- -------------------- ---------- --------------------

10 CLARK 2450

10 KING 5000 CLARK

10 MILLER 1300 KING

20 ADAMS 1100

20 FORD 3000 ADAMS

20 JONES 2975 FORD

20

SCOTT 3000 JONES

20 SMITH 800 SCOTT

30 ALLEN 1600

30 BLAKE 2850 ALLEN

30 JAMES 950 BLAKE

30 MARTIN 1250 JAMES

30 TURNER 1500 MARTIN

30 WARD 1250 TURNER

已选择14行。

select 'hanghao' as hanghao,

(select x.name

from (select ROW_NUMBER() OVER(ORDER BY t.name) as row_number, t.*

from pp t

where t.parent_id = '0') x

where x.row_number = 1),

(select x.name

from (select ROW_NUMBER() OVER(ORDER BY t.name) as row_number, t.*

from pp t

where t.parent_id = '0') x

where x.row_number > 1)

from dual

union all (select '1' as hanghao,

(select x.name

from (select ROW_NUMBER() OVER(ORDER BY t.name) as row_number,

t.*

from pp t

where t.parent_id = '1') x

where x.row_number = 1

),

(

select x.name

from (select ROW_NUMBER() OVER(ORDER BY t.name) as row_number,

t.*

from pp t

where t.parent_id = '4') x

where x.row_number = 1

)

from dual)

union all

(select '2' as hanghao,

(select x.name

from (select ROW_NUMBER() OVER(ORDER BY t.name) as row_number,

t.*

from pp t

where t.parent_id = '1') x

where x.row_number > 1),

(select x.name

from (select ROW_NUMBER() OVER(ORDER BY t.name) as row_number,

t.*

from pp t

where t.parent_id = '4') x

where x.row_number > 1)

from dual)