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

1、将 grade_info 表中的 grade_num 字段进行变动，根据 type 条件，将值为 reduce 的 grade_num 调整为负数，便于后续聚合运算

select
    user_id,
    if(type='add', grade_num, grade_num * (-1)) as grade_num_new
from grade_info 

2、将1的结果聚合运算，获得最终各 id 的得分

select
    sum(g1.grade_num_new) as grade_sum_grade
from (
    select
        user_id,
        if(type='add', grade_num, grade_num * (-1)) as grade_num_new
    from grade_info  
      ) as g
group by g.user_id

3、将2的结果与 user 表进行 join，获得包含 name 字段的结果

select
    gi.user_id,
    u.name,
    gi.grade_sum
from (
    select
        g.user_id,
        sum(g.grade_num_new) as grade_sum
    from (
        select
            user_id,
            if(type='add', grade_num, grade_num * (-1)) as grade_num_new
        from grade_info
         ) as g
    group by g.user_id
     ) as gi
join user as u
on u.id = gi.user_id

4、由于最终结果只需要获得最高得分的用户信息，需要选出最高分

select
    max(grade_sum_grade) as grade_sum
from (
    select
        sum(g1.grade_num_new) as grade_sum_grade
    from (
        select
            user_id,
            if(type='add', grade_num, grade_num * (-1)) as grade_num_new
        from grade_info  
          ) as g1
group by g1.user_id
     ) as g2

5、使用 where 对2的结果进行筛选，获取最高最终得分的用户相关信息，即最终结果

select
    gi.user_id,
    u.name,
    gi.grade_sum
from (
    select
        g.user_id,
        sum(g.grade_num_new) as grade_sum
    from (
        select
            user_id,
            if(type='add', grade_num, grade_num * (-1)) as grade_num_new
        from grade_info
         ) as g
    group by g.user_id
     ) as gi
join user as u
on u.id = gi.user_id
where gi.grade_sum = (
                      select
                          max(grade_sum_grade) as grade_sum
                      from (
                          select
                              sum(g1.grade_num_new) as grade_sum_grade
                          from (
                            select
                                user_id,
                                if(type='add', grade_num, grade_num * (-1)) as grade_num_new
                          from grade_info  
                               ) as g1
                          group by g1.user_id
                      ) as g2) 
order by gi.user_id