我们能知道最多的人只有1个的时候怎么写,当有多个的时候,那么我们先获取这个最大值,比如4,4,3,3,3,先获得4是最大值,那么再获得所有的,其中等于4的,就按id升序输出。
获取最大值SQL:
select sum(grade_num) as grade_sum from grade_info group by user_id order by grade_sum desc limit 1
获得所有人的积分的SQL:
select user_id,sum(grade_num) as grade_sum from grade_info group by user_id
select user_id,sum(grade_num) as grade_sum from grade_info group by user_id having grade_sum=(select sum(grade_num) as grade_sum from grade_info group by user_id order by grade_sum desc limit 1);
再联立user表得到结果:
select user.id, user.name,t.grade_sum from( select user_id,sum(grade_num) as grade_sum from grade_info group by user_id having grade_sum=(select sum(grade_num) as grade_sum from grade_info group by user_id order by grade_sum desc limit 1) ) t join user on t.user_id=user.id order by user.id