Simone
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
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行。
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
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行。
