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

SELECT
b.id,
b.name,
r_table.grade_num
FROM(
    SELECT
    a.user_id,
    a.grade_num,
    RANK() OVER(order by a.grade_num DESC) g_rank
    FROM(
        SELECT 
        user_id,
        SUM(
            CASE WHEN type = 'add'
            THEN grade_num * 1
            ELSE grade_num * -1
            END
        ) grade_num
        FROM grade_info 
        GROUP BY user_id
    ) a
) r_table
LEFT JOIN user b
ON r_table.user_id = b.id
WHERE r_table.g_rank = 1