对表中人员的score进行求和然后计算出名次字段
对应的mysql语句为:
select personID, totalScore,personName,
if(@de > totalScore, @rank:=@rank+@num+1, @rank) rank,
if(@de = totalScore, @num:=@num, @num:=0),
@de:=totalScore from(
select @de:=0, @rank:=1, @num:=0,personName,personID,sum(score) totalScore
from person
group by personID,personName order by totalScore desc) tmp
执行之后计算出的结果为:
rank为统计排名的名次。
名次有的需求或许不是需要 1 2 2 3形式的排名,而是需要 1 2 2 4
那么只需要把sql语句改为
select personID, totalScore,personName,
if(@de > totalScore, @rank:=@rank+@num+1, @rank) rank,
if(@de = totalScore, @num:=@num+1, @num:=0),
@de:=totalScore from(
select @de:=0, @rank:=1, @num:=0,personName,personID,sum(score) totalScore
from person
group by personID,personName order by totalScore desc) tmp
即可
结果显示为:
如果需要给查询的数据加条件,比如只对personID 为1001,1003的人员进行统计排名:
select personID, totalScore,personName,
if(@de > totalScore, @rank:=@rank+@num+1, @rank) rank,
if(@de = totalScore, @num:=@num, @num:=0),
@de:=totalScore from(
select @de:=0, @rank:=1, @num:=0,personName,personID,sum(score) totalScore
from person where personID in ("1001","1003")
group by personID,personName order by totalScore desc) tmp
结果为:
接下来是统计某人的数据以及排名情况:
select personID, totalScore, personName, rank from (
select personID, totalScore,personName,
if(@de > totalScore, @rank:=@rank+@num+1, @rank) rank,
if(@de = totalScore, @num:=@num, @num:=0),
@de:=totalScore from(
select @de:=0, @rank:=1, @num:=0,personName,personID,sum(score) totalScore
from person
group by personID,personName order by totalScore desc) tmp ) tmp2 where personID="1003"
计算结果为: