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

sum 开窗 + if判断

WITH grade_new AS (
    SELECT
       *,sum(if(gi.type ='reduce',gi.grade_num * -1,gi.grade_num)) OVER (PARTITION BY name) rk
FROM user
LEFT JOIN grade_info gi
ON user.id = gi.user_id
)
SELECT
    DISTINCT gn.id,gn.name,gn.rk grade_num
FROM grade_new gn
WHERE gn.rk = (SELECT max(rk) FROM grade_new)
ORDER BY gn.id