MySQL实现连续排序，顺序排序，跳跃排序

数据表如下：

①DENSE_RANK()排序

oracle中的dense_rank()排序

排序效果如下：

MySQL实现的语句代码如下：

方法一：

SET @RANK=0;

SET @PREScore=0;

SELECT Score,

CASE

WHEN @PREScore=Score THEN @RANK

WHEN @PREScore:=Score THEN @RANK:=@RANK+1

END AS RANK

FROM scores

ORDER BY Score DESC;  

方法二：

SET @RANK=0;

SET @PREScore=0;

SELECT Score,RANk FROM(

SELECT Score,IF(@PREScore=Score,@RANK,@RANK:=@RANK+1) AS RANK,

@PREScore:=Score

FROM scores

ORDER BY Score DESC)S;  

②ROW_NUMBER()

oracle中的row_number()排序

排序效果如下：

MySQL实现的语句代码如下：

SET @RANK=0;

SELECT Score,@RANK:=@RANK+1

FROM scores

ORDER BY Score DESC;

 

③RANK()排序

oracle中的rank()排序

排序效果如下：

MySQL实现的语句代码如下：

SET @RANK=0;

SET @PREScore=0;

SET @INCRANK=1;

SELECT Score,RANK FROM(

SELECT Score,@RANK:=IF(@PREScore=Score,@RANK,@INCRANK) AS RANK,

@PREScore:=Score,

@INCRANK:=@INCRANK+1

FROM scores

ORDER BY Score DESC

)S;