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

SELECT
    u.id,
    u.name,
    t.grade_num
FROM
    user u
    JOIN (
        SELECT
            user_id,
            SUM(
                CASE
                    WHEN type = 'add' THEN grade_num
                    ELSE - grade_num
                END
            ) grade_num
        FROM
            grade_info
        GROUP BY
            user_id
    ) t ON u.id = t.user_id
WHERE
    t.grade_num = (
        SELECT
            MAX(grade_num)
        FROM
            (
                SELECT
                    user_id,
                    SUM(
                        CASE
                            WHEN type = 'add' THEN grade_num
                            ELSE - grade_num
                        END
                    ) grade_num
                FROM
                    grade_info
                GROUP BY
                    user_id
            ) m
    )
ORDER BY
    u.id;
    select t1.id,t1.name,t0.grade as grade_sum
from(
    select user_id,grade,rank()over(order by grade desc) as t
    from(
        select user_id,sum(if(type='add',grade_num,-1*grade_num)) as grade
        from grade_info
        group by user_id
        ) a0
) t0
join user as t1
on t0.user_id = t1.id
where t=1