case when... then... else ...end 创建新列

with t as(
    select
        u.id,
        name,
        case when g.type='reduce' then grade_num*(-1) else grade_num end grade_num 
    from user u
    right join grade_info g on u.id=g.user_id
),
t1 as(
    select
        distinct id,
        name,
        sum(grade_num) over(partition by id) grade_num
    from t
),
t2 as(
    select
        *,
        rank() over(order by grade_num desc) r
    from t1
)
select id,
    name,
    grade_num
from t2
where r=1
order by id