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

select
    user_id,
    name,
    grade_num
from(
        select
            distinct(user_id),
            name,
            grade_num,
            max(grade_num) over() as max_num
        from(
                select
                    user_id,
                    name,
                    sum(grade_num) over(partition by user_id order by user_id) as grade_num
                from(
                        select
                            user_id,
                            name,
                            case when type='reduce' then (0-grade_num)
                                else grade_num
                            end as grade_num,
                            type
                        from 
                            user u,
                            grade_info g
                        where
                            u.id = g.user_id
                    ) a
            ) b
) c
where 
    c.grade_num = c.max_num