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

第一步，做计算

SELECT user_id,SUM(CASE type WHEN 'add' THEN grade_num
                             ELSE -1*grade_num END) grade_sum FROM grade_info g
         group by user_id

第二步，窗口函数排序

SELECT u.id,u.name,grade_sum,RANK()OVER(ORDER BY g1.grade_sum DESC ) AS R
    FROM (SELECT user_id,SUM(CASE type WHEN 'add' THEN grade_num
                             ELSE -1*grade_num END) grade_sum FROM grade_info g
         group by user_id) g1

第三步，联立表user

SELECT u.id,u.name,grade_sum,RANK()OVER(ORDER BY g1.grade_sum DESC ) AS R
    FROM (SELECT user_id,SUM(CASE type WHEN 'add' THEN grade_num
                             ELSE -1*grade_num END) grade_sum FROM grade_info g
         group by user_id) g1
    JOIN user u ON g1.user_id=u.id) g2

第四步，调取结果

SELECT id,name,grade_sum
FROM (SELECT u.id,u.name,grade_sum,RANK()OVER(ORDER BY g1.grade_sum DESC ) AS R
    FROM (SELECT user_id,SUM(CASE type WHEN 'add' THEN grade_num
                             ELSE -1*grade_num END) grade_sum FROM grade_info g
         group by user_id) g1
    JOIN user u ON g1.user_id=u.id) g2
    WHERE R=1