题解 | #获得积分最多的人(三)#
获得积分最多的人(三)
http://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
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
投递阿里巴巴等公司10个岗位 >