获取排名列表:

$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));

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

需求:获取分类平均值的名次? 比如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