mysql给数据做排名_mysql给数据统计做排名

1、问题描述

目前有一个表tb_rank(PlayerID,PlayerName,Score) PlayerID是Primary key(主键)；

现在的需求是，希望在表tb_rank中再添加一列rankNum，这个rankNum记录的是Score的排名

(根据分数做降序排列)。

2、初步解决方法(不考虑Score相同的情况)

问题分析：

经过分析，我们可以将此问题大概分解为以下三个子问题：

(1) 在表tb_rank中增加一列rankNum；

ALTER TABLE tb_rank ADD rankNum INT;

(2)对表tb_rank中的Score列进行降序排名，且将排名数值增加到被排序的元组中；

初次执行排序可以使用：

SELECT PlayerID,PlayerName,Score,(@rank:=IFNULL(@rank,0)+1) rankNum

FROM tb_rank

ORDER  BY Score DESC

普遍使用的排序SQL为：

SET @rank:=0;

SELECT PlayerID,PlayerName,Score,(@rank:=@rank+1) rankNum

FROM tb_rank

ORDER  BY Score DESC

(3)用第2步的结果来更新新增列rankNum的值。

SET @rank:=0;

UPDATE tb_rank,(select PlayerID,( @rank:=@rank+1) rankNum FROM tb_rank ORDER  BY score DESC) temp_tb_rank

SET tb_rank.rankNum=temp_tb_rank.rankNum

WHERE tb_rank.PlayerID=temp_tb_rank.PlayerID;

排名的tb_rank表查询返回的结果为：

到此就实现了简单的排名了。但是这里存在一个问题，就是说当score相同的时候，

比如：

PlayerID是’12‘和’13‘以及’123456‘，它们的Score都是1000，

但是查询出来的结果在排名上的呈现却不一致。

这个是不合理的。下面第3节介绍一种解决方法。

3、更进一步的解决方法

实现思路：

产生第二步中同样的分数出现不同的排名的问题，其根因是排名实现没有考虑分数相同的情况，

下面增加一个变量@preScore来保存产生当前元组时，上一个元组的分数；然后在生成排名的列，

根据当前元组的分数与上一个元组的分数大小进行比较，如果相等，那么排名不变，否则排名加1。

编写SQL语句需要注意一点，对变量@preScore的赋值需要放到排名列rankNum之后，

要让其先生成排名，而后才可以更新@preScore变量。

(从SQL语句的结果生成原理上看，SELECT语句中，写在前面的列值是先生成，

写在后面的列值是后得到的。例如，下面的SELECT语句中，

PlayerID的位置放在最前面，那么实际返回查询结果的时候，是先获取PlayerID的值的。)

SET @rank:=0;

SET @preScore:=0;

SELECT PlayerID,( IF( @preScore<>Score,@rank :=@rank+1,@rank ) ) rankNum,@preScore:=Score FROM tb_rank ORDER BY score DESC

最后合成后的SQL语句为：

SET @rank:=0;

SET @preScore:=0;

UPDATE tb_rank, (SELECT PlayerID,( IF( @preScore<>Score,@rank:=@rank+1,@rank ) ) rankNum,@preScore:=Score

FROM tb_rank

ORDER  BY score DESC) temp_tb_rank

SET tb_rank.rankNum=temp_tb_rank.rankNum

WHERE tb_rank.PlayerID=temp_tb_rank.PlayerID;