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

WITH 
-- 计算用户增加的分数
temp_add AS (
    SELECT u1.id, u1.name, 
        SUM(g.grade_num) AS grade_add
    FROM user u1
    JOIN grade_info g ON u1.id = g.user_id
    WHERE type = "add"
    GROUP BY u1.id, u1.name
),
-- 计算用户减少的分数
temp_reduce AS (
    SELECT u2.id, u2.name, 
        SUM(g2.grade_num) AS grade_reduce
    FROM user u2
    JOIN grade_info g2 ON u2.id = g2.user_id
    WHERE type = "reduce"
    GROUP BY u2.id, u2.name
),
-- 计算用户最终得分
temp_total as(
    SELECT a.id, a.name, a.grade_add - COALESCE(r.grade_reduce, 0) AS grade_num
    FROM temp_add a left join temp_reduce r ON a.id = r.id
)
-- 选择最高得分用户
select * from temp_total where grade_num in(
    select max(grade_num) from temp_total
)