Oracle中排序函数的用法之ROW_NUMBER()_RANK()_DENSE_RANK() OVER()的区别

一、ROW_NUMBER()的用法
语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER() OVER (ORDER BY colum DESC) 是先把colum列降序,再为降序以后的每条colum记录返回一个序号。
ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的,没有重复值)。
row_number():返回的是行信息,没有排名
rank():返回的相关等级不会跳跃
dense_rank():返回的相关等级会跳跃


SELECT empno,sal,
rank() over(order by sal) rank,
dense_rank() over(order by sal) dense_rank,
row_number() over(order by sal) row_number
FROM emp;
mysql实现方式:
SET @rk=0;
SET @deptno=0;
SELECT ename,
sal,
@rk:=IF(@deptno=deptno,@rk+1,1) rk,
@deptno:=deptno deptno
FROM emp
ORDER BY deptno,sal DESC;
二、RANK()的用法
语法:RANK() OVER (PARTITION BY COL1 ORDER BY COL2)
RANK()的用法和ROW_NUMBER()类似,只不过RANK()是跳跃排序,例如:有两个第三名时接下来就是第五名(同样是在各个分组内)。
SELECT empno,sal,
rank() over(order by sal desc) rank
FROM emp;
上表例子中所用的表为Oracle中自带的数据表emp,如使用rank()函数排序的结果可以看出,其按降序(或升序)排名后,是一个跳跃排序的,如上图中红框中所示。
SELECT b.empno,b.sal,
(SELECT count(a.sal)
from emp a
WHERE b.sal < a.sal)+1 rk
from emp b
ORDER BY sal desc;
其中,在Mysql中还可以使用如下的语句来实现这个排序:
set @rk=0;
select ename,
@rk:=@rk+1 rk,
sal
from emp
order by sal desc;
------------------------------------------------------------------------
select a.*,
@enum:=IF(@bm=a.deptno,@enum+1,1) enum,
@rank:= case when @bm <> a.deptno then 1
when @xz <> a.sal then @enum
else @rank end rk,
@bm:=a.deptno bm, # 变量初始化
@xz:=a.sal xz
from
(select deptno,empno,sal from emp) a,
(select @bm:=null,@rank:=1,@xz:=null,@enum:=0) b
order by deptno,sal desc
三、DENSE_RANK()的用法
语法:DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
DENSE_RANK()的用法和ROW_NUMBER()类似,只不过DENSE_RANK()是连续排序,有两个第二名时仍然跟着第三名(同样在各个分组内)。
SELECT empno,sal,
dense_rank() over(order by sal desc) dense_rank
FROM emp;
同上,本函数继续使用数据表emp。可以看出,dense_rank()对sal进行降序排序后,排序值是连续的。
SELECT b.empno,b.sal,
(SELECT count(distinct a.sal)
from emp a
WHERE b.sal < a.sal)+1 rk
from emp b
ORDER BY sal desc;
此SQL语句同样可以实现dense_rank()的效果,与rank()中实现的区别在于count()函数中第三部分使用了distinct函数来去重,从而达到了排序值是连续的。
同样,本案例也可以使用Mysql语句来实现,如下:
set @rk=0;
set @sal=0;
select ename,
@rk:=if(@sal=sal,@rk,@rk+1) rk,
@sal:=sal sal
from emp
order by sal desc;
————————————————
select a.*,
@rank:= case when @bm <> a.deptno then 1
when @xz <> a.sal then @rank+1
else @rank end rk,
@bm:=a.deptno bm, # 变量初始化
@xz:=a.sal xz
from
(select deptno,empno,sal from emp) a,
(select @bm:=null,@rank:=1,@xz:=null) b
order by deptno,sal desc
 
————————————————
原文链接:https://blog.csdn.net/Jason_05/article/details/90743928
原文地址:https://www.cnblogs.com/qinjiting/p/11603492.html