数据表如下:
①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;