分组
select user_id, sum(if(gi.type = 'add', gi.grade_num, -gi.grade_num)) grade_num from grade_info gi group by gi.user_id排名
select user_id ,@a:=@a +(@pre<>@pre :=grade_num) as t,b.grade_num from (select user_id, sum(if(gi.type = 'add', gi.grade_num, -gi.grade_num)) grade_num from grade_info gi group by gi.user_id )b ,(select @a:=0,@pre :=-1)a order by b.grade_num desc
关联user
select u.id,u.name,c.grade_num from user u join ( select user_id ,@a:=@a +(@pre<>@pre :=grade_num) as t,b.grade_num from (select user_id, sum(if(gi.type = 'add', gi.grade_num, -gi.grade_num)) grade_num from grade_info gi group by gi.user_id )b ,(select @a:=0,@pre :=-1)a order by b.grade_num desc )c on u.id=c.user_id where t=1 order by u.id ;