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

问题描述：请你写一个SQL查找积分增加最高的用户的id(可能有多个)，名字，以及他的总积分是多少，查询结果按照id升序排序，以上例子查询结果如下:

方案1：分组求和grade_sum,添加窗口函数sum_rank

WITH t AS(
     SELECT user_id,SUM(grade_num) AS grade_sum,
         DENSE_RANK() OVER(ORDER BY SUM(grade_num) DESC) AS sum_rank
     FROM grade_info
     WHERE type = 'add'
     GROUP BY user_id
     ) 
SELECT u.id,u.name,t.grade_sum
FROM user u
INNER JOIN t
ON u.id = t.user_id
WHERE t.sum_rank = 1
ORDER BY u.id ASC;

方案2：采用分组求和grade_num,采用MAX()函数求最大的grade_sum,联立三个表

WITH t_sum AS (
          SELECT user_id,SUM(grade_num) AS grade_sum
          FROM grade_info
          WHERE type = 'add'
          GROUP BY user_id
          )
SELECT u.id,u.name,ts.grade_sum
FROM user u
INNER JOIN t_sum ts
ON u.id = ts.user_id
WHERE ts.grade_sum = (SELECT MAX(t_sum.grade_sum)
                     FROM t_sum)
ORDER BY u.id ASC;