mysql 成绩排名 名次 及 lag函数方法使用

-- SELECT *  FROM employment_direction_recommend_table GROUP BY student_name HAVING count(student_name)>1;
-- SELECT * FROM employment_direction_recommend_table WHERE student_name in(‘东青梁‘,‘桑哲青‘)

-- 计算平均成绩及名次
SELECT a.student_name,

-- @lagfield as 上一个成绩,

case when a.`平均成绩`=@lagfield then @rowNum else (@rowNum:=@rowNum+1) END as `排名`,

@lagfield:=a.`平均成绩` as 成绩

-- (@rowNum:=@rowNum+1) as `名次`   
FROM(
select student_name,
round((avg(required_synthesize)+avg(major_synthesize)+avg(synthesize_appraisal))/3,2) as `平均成绩`
from employment_direction_recommend_table b GROUP BY student_name order by `平均成绩` desc
) a,(SELECT (@rowNum :=0)) b,(select @lagfield:=0) r