用sql实现统计排名

你将斩获：

• 通过这个案例体会使用逻辑构建来实现需求的示例
• 感知设立初始化变量的作用和意义，以及一些通用技巧的应用

-- 建表操作
create table scores_tb (
    id int  auto_increment primary key,
    xuehao int not null, 
    score int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
insert into scores_tb (xuehao,score) values (1001,89),(1002,99),(1003,96),(1004,96),(1005,92),(1006,90),(1007,90),(1008,94);

# 查看下插入的数据
select * from scores_tb;

/***************************case1***********************************/

# 1.普通排名
-- 按分数高低直接排名，从1开始，往下排，类似于row number。下面我们给出查询语句及排名结果
select  xuehao
			 ,score
			 ,(@rank := @rank + 1) as ranking
from scores_tb join (select @rank := 0)t
order by score desc;

/**************************case2************************************/


# 2.分数相同，名次相同，排名无间隔

-- 实际上，下面的代码中"@prevRank = score"是恒不成立的,所以始终执行条件为否的命令行
-- 结果和代码实际上等同case1

select  xuehao
			 ,score
			 ,if(@prevRank = score,@rank,@rank:= @rank + 1) as ranking
from scores_tb join (select @rank := 0,@prevRank := null)t
order by score desc;

-- 原因分析：并非属于是否的条件逻辑判断问题

/**************************************************************/

-- 代码修改

select  xuehao
			 ,score
			 ,if(@prevRank = score,@rank,@rank:= @rank + 1) as ranking
			 ,@prevRank := score as prevRank
from scores_tb join (select @rank := 0,@prevRank := "")t
order by score desc;


# 3.并列排名，排名有间隔
-- 另外一种排名方式是相同的值排名相同，相同值的下一个名次应该是跳跃整数值，即排名有间隔。

select  xuehao,score,ranking from
(select  xuehao
			  ,score
			  ,@rank := if(@prevRank = score,@rank,@incrank) as ranking
			  ,@incrank := @incrank + 1
			  ,@prevRank := score as prevRank
from scores_tb join (select @rank := 0,@prevRank := "",@incrank:= 1)t
order by score desc)t1

# 4.MySQL8.0 利用窗口函数实现排名

# 三条语句对于上面三种排名
select xuehao,score, ROW_NUMBER() OVER(order by score desc) as row_r from scores_tb;
select xuehao,score, DENSE_RANK() OVER(order by score desc) as dense_r from scores_tb;
select xuehao,score, RANK() over(order by score desc) as r from scores_tb;

# 一条语句也可以查询出不同排名
SELECT xuehao,score,
    ROW_NUMBER() OVER w AS 'row_r',
    DENSE_RANK() OVER w AS 'dense_r',
    RANK()       OVER w AS 'r'
FROM `scores_tb` 
WINDOW w AS (ORDER BY `score` desc);

-- 注意:其实在这部分，我们可以进行推理或者归纳得到隐形知识，即代码中判断及赋值语句的执行顺序是在order by 之后;

参考