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

#使用if（条件，结果1，反之结果）
select id,name,grade_num
from(
    select user_id id,name,t.grade_num grade_num,rank()over(order by grade_num desc)num
    from user u left join
        (select user_id,sum(if(type = "add",grade_num,-1*grade_num)) grade_num
        from grade_info
        group by user_id
        )t 
        on u.id=t.user_id
    order by t.grade_num desc 
    )t2
where num=1
order by id

#使用case when
select id,name,grade_num
from(
    select user_id id,name,t.grade_num grade_num,rank()over(order by grade_num desc)num
    from user u left join
        (select user_id,sum(case type when "add" then grade_num else -1*grade_num end) grade_num
        from grade_info
        group by user_id
        )t 
        on u.id=t.user_id
    order by t.grade_num desc 
    )t2
where num=1
order by id