#排名(原文)
需求:获取分类平均值的名次? 比如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’