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;

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