ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD OVER()函数

最新推荐文章于 2024-10-04 22:00:00 发布

刨冰雨

最新推荐文章于 2024-10-04 22:00:00 发布

阅读量777

点赞数

文章标签： null sql c

转载: http://hi.baidu.com/122439049/blog/item/0c9c48131b2734d5f7039e13.html

ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，

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

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

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

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

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

LAG（arg1,arg2,arg3)：

arg1是从其他行返回的表达式；

arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目；

arg3是在arg2表示的数目超出了分组的范围时返回的值；

LEAD(value_expr [,offset][,default])
value_expr 值表达式，通常是字段，也可是是表达式。value_expr本身不支持分析函数，也就是lead不支持多层调用。
offset 偏移，应该是很熟悉的数学概念了，或者是相对偏移，表格来开当前行的第offset行，如果offset是整数就表示是顺序下的前第n行，如果是负数就是往后第n行。如果不提供这个参数，就是默认为1.
default 默认值，如果没有找到，应该返回什么值的意思，有点类似nvl(col,value)。如果没有设置，且找不到，那么就返回Null

语句一：按照sale/cnt 排序

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
);

执行结果：

语句二：查询员工的工资,按部门排名

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

执行结果：

1 KING 5000.00 1
2 CLARK 2450.00 2
3 MILLER 1300.00 3
4 SCOTT 3000.00 1
5 FORD 3000.00 2
6 JONES 2975.00 3
7 ADAMS 1100.00 4
8 SMITH 800.00 5
9 BLAKE 2850.00 1
10 ALLEN 1600.00 2
11 TURNER 1500.00 3
12 WARD 1250.00 4
13 MARTIN 1250.00 5
14 JAMES 950.00 6

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

select * from 
(
  select empno, ename, sal, deptno, row_number() over(partition by deptno order by sal) as sal_order
  from scott.emp t
) tb
where tb.sal_order < 2;

执行结果：

1 7934 MILLER 1300.00 10 1
2 7369 SMITH 800.00 20 1
3 7900 JAMES 950.00 30 1

语句四：按照部门销售总额排名

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

执行结果：

1 MILLER 7934 10 1300.00 1
2 CLARK 7782 10 2450.00 2
3 KING 7839 10 5000.00 3
4 SMITH 7369 20 800.00 1
5 ADAMS 7876 20 1100.00 2
6 JONES 7566 20 2975.00 3
7 SCOTT 7788 20 3000.00 4
8 FORD 7902 20 3000.00 4
9 JAMES 7900 30 950.00 1
10 WARD 7521 30 1250.00 2
11 MARTIN 7654 30 1250.00 2
12 TURNER 7844 30 1500.00 4
13 ALLEN 7499 30 1600.00 5
14 BLAKE 7698 30 2850.00 6

语句五：DENSE_RANK()

select t.ename, t.empno, t.deptno, t.sal,
       dense_rank() over(partition by deptno order by sal) as dense_rank_order 
from scott.emp t 
where deptno=20
order by deptno;

执行结果：
1 SMITH 7369 20 800.00 1
2 ADAMS 7876 20 1100.00 2
3 JONES 7566 20 2975.00 3
4 SCOTT 7788 20 3000.00 4
5 FORD 7902 20 3000.00 4

语句六：LAG() OVER()

select deptno,ename,sal,
       lag(ename,1,'部门销售额第一名') over(partition by deptno order by sal desc) as commnet 
from scott.emp order by deptno;

执行结果：

1 10 KING 5000.00 部门销售额第一名
2 10 CLARK2450.00KING
3 10 MILLER1300.00CLARK
4 20 SCOTT3000.00部门销售额第一名
5 20 FORD3000.00SCOTT
6 20 JONES2975.00FORD
7 20 ADAMS1100.00JONES
8 20 SMITH800.00ADAMS
9 30 BLAKE2850.00部门销售额第一名
10 30 ALLEN1600.00BLAKE
11 30 TURNER1500.00ALLEN
12 30 WARD1250.00TURNER
13 30 MARTIN1250.00WARD
14 30 JAMES950.00MARTIN

语句七：LEAD() OVER()

select deptno, ename, sal
       ,lead(ename, 2, '部门销售排名后两名') over(partition by deptno order by sal desc) as comments
       ,row_number() over(partition by deptno order by sal desc) as order_dept_sal
from scott.emp

DEPTNO ENAME SAL COMMENTS ORDER_DEPT_SAL
10 KING 5000.00 MILLER 1
10 CLARK 2450.00 部门销售排名后两名 2
10 MILLER 1300.00 部门销售排名后两名 3
20 SCOTT 3000.00 JONES 1
20 FORD 3000.00 ADAMS 2
20 JONES 2975.00 SMITH 3
20 ADAMS 1100.00 部门销售排名后两名 4
20 SMITH 800.00 部门销售排名后两名 5
30 BLAKE 2850.00 TURNER 1
30 ALLEN 1600.00 WARD 2
30 TURNER 1500.00 MARTIN 3
30 WARD 1250.00 JAMES 4
30 MARTIN 1250.00 部门销售排名后两名 5
30 JAMES 950.00 部门销售排名后两名 6

语句八：ROW_NUMBER() OVER() 分页查询

select * from 
(
       select t.*, row_number() over(order by empno) pageNum
       from scott.emp t
) tb
where tb.pageNum between 5 and 10;

执行结果：

1 7654 MARTIN SALESMAN76981981/9/281250.001400.00305
2 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 6
3 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7
4 7788 SCOTT ANALYST 7566 1987/4/193000.00208
5 7839 KING PRESIDENT1981/11/175000.00109
6 7844 TURNER SALESMAN76981981/9/81500.000.003010