获取排名列表：

$rankDefault = intval(($data['currentPage'] - 1) * $data['currentCount']);

pdo_fetchall("select @rank:=ifnull(@rank,0)+1 as rank,`id`,`openid`,`username`,`score`,`create_time` from (SELECT @rank := ".$rankDefault.") r, " . tablename($r_config['mysql']['tableUser']) . " as u $where order by score desc LIMIT " . ($data['currentPage'] - 1) * $r_config['function']['currentCount'] . ',' . $r_config['function']['currentCount']);

获取我的排名：

pdo_fetchcolumn("select rank FROM (SELECT @rank:=ifnull(@rank,0)+1 as rank,`id`,`openid`,`username`,`score`,`create_time`,`status` from (SELECT @rank :=0) r, " . tablename($r_config['mysql']['tableUser']) . " as u order by score desc) as uu $where ",array(':openid'=>$openid));

$getRankingData = pdo_fetchall("select * FROM (select @rank:=ifnull(@rank,0)+1 as rank,openid,username,image,kilometre,createtime FROM (SELECT id,`openid`,sum(`title`) as kilometre,username,image,createtime from (SELECT @rank :=0 ) r, " . tablename($r_config['mysql']['newpicshareMessageTable']) . " as mt where mt.note = :note AND mt.status = :status group by openid order by kilometre desc,createtime asc,id asc ) as mtt ) as mttt",array(':note'=>3,':status'=>1));

$myRankingData = pdo_fetchcolumn("select rank FROM (select @rank:=ifnull(@rank,0)+1 as rank,openid,kilometre,createtime FROM (SELECT id,`openid`,sum(`title`) as kilometre,createtime from (SELECT @rank :=0 ) r, " . tablename($r_config['mysql']['newpicshareMessageTable']) . " as mt where mt.note = :note AND mt.status = :status group by openid order by kilometre desc,createtime asc,id asc ) as mtt ) as mttt where mttt.openid = :openid",array(':note'=>3,':status'=>1,':openid'=>$openid));

--------------------------------------------------------------参考------------------------------------------------

需求：获取分类平均值的名次？ 比如10个班级的平均分，按照班级名称排序，后面跟着名次。

记录表：table_test ；

字段：banji 班级；AvgS 平均分；pm 排名；

解决方法：

FROM
(
SELECT A.*,@rank:=@rank+1 as pm
FROM
(
SELECT banji,avg(score) as AvgS FROM table_test GROUP BY banji ORDER BY AvgS DESC
) A ,(SELECT @rank:=0) B
) M
ORDER BY M.banji
==============================>>>>>>>>输出结果：
banji AvgS pm
-----------------------

'1', '47.0000', '1'
'2', '12.0000', '3'
'5', '38.5000', '2'

原文链接：https://blog.csdn.net/k8080880/article/details/11253305