SQL89 获得积分最多的人(一) SQL90 获得积分最多的人(二)SQL91 获得积分最多的人(三)

---

一、SQL89 获得积分最多的人(一)

牛客每天有很多用户刷题，发帖，点赞，点踩等等，这些都会记录相应的积分。
有一个用户表(user)，简况如下：

还有一个积分表(grade_info)，简况如下:

请你写一个SQL查找积分增加最高的用户的名字，以及他的总积分是多少(此题数据保证积分最高的用户有且只有1个)，以上例子查询结果如下:

(1) with创建临时表

WITH t1 as(
    SELECT user_id,sum(grade_num) grades
    FROM grade_info
    GROUP BY user_id
)

SELECT user.name,grades
FROM t1 JOIN user
ON t1.user_id=user.id
WHERE grades=(
        SELECT max(grades)
        FROM t1)

（2）LIMIT OFFSET/dense_rank

SELECT user.name,sum_count
FROM
    (
    SELECT user_id,
        sum(grade_num) OVER(PARTITION BY user_id) sum_count
    FROM grade_info
    ORDER BY sum_count DESC
    LIMIT 1
    ) t1
JOIN user
ON t1.user_id=user.id;

2. SQL90 获得积分最多的人(二)

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

(1) 嵌套

SELECT distinct user_id,name,count_num
FROM
    (
        SELECT user_id,count_num,
               dense_rank() over(order by count_num DESC) as t_rank
        FROM(
            SELECT user_id,
                   sum(grade_num) over(partition by user_id) as count_num
            FROM grade_info
            ) t
    ) t1 
JOIN user
ON user_id=id
WHERE t1.t_rank=1
ORDER BY id;

（2）优化：
将求总新增次数与对总新增次数排名，写在一个查询语句中。

SELECT distinct user_id,name,count_num
FROM
    (
     SELECT user_id,
            sum(grade_num) as count_num,
            DENSE_RANK() over(order by sum(grade_num) DESC) as t_rank
     FROM grade_info
     GROUP BY user_id
    ) t1 
JOIN user
ON user_id=id
WHERE t1.t_rank=1
ORDER BY id;

（3） WITH临时表

WITH t1 as(
    SELECT user_id,sum(grade_num) grades
    FROM grade_info
    GROUP BY user_id
)

SELECT user_id,user.name,grades
FROM t1 JOIN user
ON t1.user_id=user.id
WHERE grades=(
        SELECT max(grades)
        FROM t1)

3.SQL91 获得积分最多的人(三)

请你写一个SQL查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序，

重点：
当type='add’时，加上相应积分，当type='reduce’时，减去相应积分
使用CASE WHEN
(1) 嵌套查询

SELECT user_id,name,grade_sum
FROM
(SELECT user_id,grade_sum,DENSE_RANK() OVER(ORDER BY grade_sum DESC) as t_rank
FROM
    (
    SELECT user_id,
        SUM(CASE WHEN type='add' THEN grade_num  WHEN type='reduce' THEN -(grade_num) END) as grade_sum
         
    FROM grade_info
    GROUP BY user_id
    ) t
) t1
JOIN user
ON t1.user_id=user.id
WHERE t_rank=1
ORDER BY user_id;

（2）临时表
1） t：用户与对应的积分总数
2）从临时表中查找出最大的积分数，查找与该积分数相等的积分数所对应的用户id
3) 与user表相连

WITH t as (
    SELECT user_id,SUM(CASE WHEN type='add' THEN grade_num 
                       ELSE -(grade_num) END) as grade_sum
    FROM grade_info
    GROUP BY user_id
)
SELECT user_id,name,grade_sum
FROM t JOIN user
ON t.user_id=user.id
WHERE grade_sum=(SELECT max(grade_sum) FROM t)