小笨蛋看过来| #获得积分最多的人(三)#

select
    user_id,
    (case type when 'add' then grade_num else -1*grade_num end) grade_num
from
grade_info

select
    id,
    name,
    grade_num
from
(
    select 
        id,
        name,
        sum(grade_num) grade_num,
        rank() over(order by sum(grade_num) desc) rk
    from
        user u 
    join 
        (
        select
            user_id,
            (case type when 'add' then grade_num else -1*grade_num end) grade_num
        from
        grade_info
        ) a
    on u.id = a.user_id
    group by name,id
    order by grade_num desc
) b
where rk = 1
order by id asc