题解 | #获得积分最多的人(三)#

select user.id, name, total_num
from
    user
left outer join
    (select user_id, sum(case type when 'add' then grade_num else  -1*grade_num end) as total_num
	from grade_info
	group by user_id
    order by total_num) as grade
on user.id = grade.user_id
group by user.id
having total_num = (select total_num
                    from (select user_id, sum(case type when 'add' then grade_num else  -1*grade_num end) as total_num
						from grade_info
						group by user_id
						order by total_num desc) as grade
                     limit 0,1)
order by user.id;

1. 窗口函数很久没复习，有一些生疏，尤其这个case()函数。

2. 仔细观察本题，其实和上一题一样，只要运用窗口函数将右联结的临时表grade形成与上一题的一样的结构，那么整体编码是不需要修改的。