Oracle分析函数总结 - 排序 - rank,dense_rank,row_number,first

这几个函数区别是：
a)rank()是跳跃排序,有两个第1名时接下来就是第3名;
b)dense_rank()是连续排序,有两个第1名时接下来仍然跟着第2名;
c)row_number()是连续排序,并且有并列名次时,按照记录集中记录的顺序名次依次递增。

www.linuxidc.com @ORCL1> select ename, sal, deptno,
2 rank() over(partition by deptno order by sal) rank,
3 dense_rank() over(partition by deptno order by sal) dense_rank,
4 row_number() over(partition by deptno order by sal) row_number
5 from emp;
ENAME SAL DEPTNO RANK DENSE_RANK ROW_NUMBER
---------- ---------- ---------- ---------- ---------- ----------
MILLER $1300.00 10 1 1 1
CLARK $2450.00 10 2 2 2
KING $5000.00 10 3 3 3
SMITH $800.00 20 1 1 1
ADAMS $1100.00 20 2 2 2
JONES $2975.00 20 3 3 3
SCOTT $3000.00 20 4 4 4
FORD $3000.00 20 4 4 5
JAMES $950.00 30 1 1 1
MARTIN $1250.00 30 2 2 2
WARD $1250.00 30 2 2 3
TURNER $1500.00 30 4 3 4
ALLEN $1600.00 30 5 4 5
BLAKE $2850.00 30 6 5 6
14 rows selected.

2) first(), last()
first,last需要和dense_rank结合使用,返回排在第一和最后的记录(集合)。语法：
aggregate_function KEEP (dense_rank first|last order by ...) [over([partition_clause])]

例如，查询各部门薪水最高和最低的人名。不带over子句, 作为聚合函数使用。

www.linuxidc.com @ORCL1> column first format a20
www.linuxidc.com @ORCL1> column last format a20
www.linuxidc.com @ORCL1> select deptno,
2 wm_concat(ename) keep (dense_rank first order by sal desc) first,
3 wm_concat(ename) keep (dense_rank last order by sal desc) last
4 from emp group by deptno;
DEPTNO FIRST LAST
---------- -------------------- --------------------
10 KING MILLER
20 SCOTT,FORD SMITH
30 BLAKE JAMES

查询各部门薪水最高和最低的人名。带over子句, 作为分析函数使用。

www.linuxidc.com @ORCL1> select ename, sal, deptno,
2 wm_concat(ename) keep (dense_rank first order by sal desc)
3 over(partition by deptno) first,
4 wm_concat(ename) keep (dense_rank last order by sal desc)
5 over(partition by deptno) last
6 from emp;
ENAME SAL DEPTNO FIRST LAST
---------- ---------- ---------- -------------------- ---------
CLARK $2450.00 10 KING MILLER
KING $5000.00 10 KING MILLER
MILLER $1300.00 10 KING MILLER
JONES $2975.00 20 FORD,SCOTT SMITH
FORD $3000.00 20 FORD,SCOTT SMITH
ADAMS $1100.00 20 FORD,SCOTT SMITH
SMITH $800.00 20 FORD,SCOTT SMITH
SCOTT $3000.00 20 FORD,SCOTT SMITH
WARD $1250.00 30 BLAKE JAMES
TURNER $1500.00 30 BLAKE JAMES
ALLEN $1600.00 30 BLAKE JAMES
JAMES $950.00 30 BLAKE JAMES
BLAKE $2850.00 30 BLAKE JAMES
MARTIN $1250.00 30 BLAKE JAMES
14 rows selected.

Oracle分析函数总结 - 排序 - rank,dense_rank,row_number,first

相关推荐