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

最近在练习CTE 准备把做过的中等及其以上难度的题目中 子查询都改成 WITH query 这题 用CTE解法 如下

WITH grade_total AS(
    SELECT user_id, 
        SUM(CASE
           WHEN type='add' THEN grade_num*1
           WHEN type='reduce' THEN grade_num*-1
           ELSE 0 
           END) as grade_sum FROM grade_info
    GROUP BY user_id),
    rank_grade AS(
        SELECT grade_total.user_id, grade_total.grade_sum, 
        RANK() OVER(ORDER BY grade_total.grade_sum DESC) as cnt
        FROM grade_total)
SELECT user.id, user.name, rg.grade_sum FROM user 
JOIN rank_grade rg ON user.id=rg.user_id 
WHERE rg.cnt=1
ORDER BY user.id ASC