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

SELECT 
    tt.user_id, 
    user.name,
    tt.sum_num 
FROM 
(
    SELECT 
        * , 
        DENSE_RANK() OVER( ORDER BY sum_num DESC ) AS rk 
    FROM 
    (
        SELECT 
            user_id, 
            SUM(
                CASE 
                    WHEN type = 'add' THEN grade_num 
                    WHEN type = 'reduce' THEN -1 * grade_num
                ELSE 0 
                END 
            ) AS sum_num 
        FROM 
            grade_info 
        GROUP BY 
            user_id 
    ) AS tmp 
) AS tt 
LEFT OUTER JOIN 
    user
ON 
    tt.user_id = user.id 
WHERE rk <=1 
;