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

SELECT
    t1.user_id,
    t2.name,
    t1.grade_num
FROM
    (SELECT
        user_id,
        SUM(CASE 
                WHEN type = 'add' THEN grade_num
                ELSE -grade_num
            END) AS grade_num
    FROM
        grade_info
    GROUP BY
        user_id) t1
LEFT JOIN
    user t2
ON
    t1.user_id = t2.id
WHERE
    t1.grade_num = (
        SELECT DISTINCT MAX(grade_num) FROM (
            SELECT
                user_id,
                SUM(CASE 
                        WHEN type = 'add' THEN grade_num
                        ELSE -grade_num
                    END) AS grade_num
            FROM
                grade_info
            GROUP BY
                user_id
        ) AS max_grades
    )
ORDER BY
    t1.user_id;