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

整体思路：与「获得积分最多的人（二）」如出一辙，唯一需要注意的add的时候需要+，reduce的时候需要-，那么在构造新表new_grade的时候需要创建临时表t，逻辑如下：

• 包含两个列 user_id 和 new_num，针对于new_num:
  • 如果type='add'，那么直接取grade_num的值
  • 如果type='reduce'，那么取grade_num的负值

SELECT user_id, case type when 'add' then grade_num
                          when 'reduce' then (-1 * grade_num) end AS new_num
  FROM grade_info

在临时表t的基础上得到包含所有用户总积分的表，记录为new_grade,逻辑如下：

WITH new_grade AS (
    SELECT t.user_id, sum(t.new_num) AS grade_sum
      FROM (SELECT user_id, case type when 'add' then grade_num
                                      when 'reduce' then (-1 * grade_num) end AS new_num
              FROM grade_info) t
  GROUP BY t.user_id
)

接着，利用dense_rank排名函数对grade_sum列按降序排名（注意，这是MySQL8.0往后的特性），得到一张临时表t1，随后与user表进行左连接（大表在前，小表在后），最后where中写上判断逻辑为只取排名为1的数据，完整代码如下：

WITH new_grade AS (
    SELECT t.user_id, sum(t.new_num) AS grade_sum
      FROM (SELECT user_id, case type when 'add' then grade_num 
                                      when 'reduce' then (-1 * grade_num) end AS new_num
              FROM grade_info) t
  GROUP BY t.user_id
)
SELECT t1.user_id AS id, t2.name, t1.grade_sum
FROM (SELECT user_id, grade_sum, dense_rank() over(order by grade_sum desc) AS d_rank 
        FROM new_grade) t1 LEFT JOIN `user` t2 ON t1.user_id = t2.id
WHERE d_rank = 1
ORDER BY t1.user_id

写在最后：

• 利用WITH table_name AS ()的形式可以简化代码量，使得逻辑更加清晰。
• dense_rank、rank、row_number三个排名函数的区别需要牢记于心，做到灵活应用。