MySQL做题实战1||获得积分最多的人_使用sql语句查询积分最多的5名-优快云博客

本文链接：https://blog.youkuaiyun.com/Inochigohan/article/details/119763039

简介：题目来自牛客网在线编程SQL实战，文章内容主要是讲解博主自己做题时的思路、相关见解以及要点总结。路漫漫其修远兮，吾将上下而求索！

一、题目内容一（难度：简单）

牛客每天有很多用户刷题，发帖，点赞，点踩等等，这些都会记录相应的积分。

有一个用户表(user)，简况如下：

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

第1行表示，user_id为1的用户积分增加了3分。

第2行表示，user_id为2的用户积分增加了3分。

第3行表示，user_id为1的用户积分又增加了1分。

.......

最后1行表示，user_id为5的用户积分增加了3分。

编写要求：

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

解释：user_id为1的总计加了4分，其他的都是3分，user_id为1的name为tm，输出tm|4

解题思路：

解题代码：

select distinct name,sum(grade_num)over(partition by user_id) as grade_sum
from grade_info
left join user
on grade_info.user_id=user.id
order by grade_sum desc
limit 0,1;

比较总结：

这道题总体上而言是比较简单的，只要熟练掌握 sum(参数)over(partition by 字段) 的用法，问题不大！
此题考查要点为窗口函数、多表连接以及limit的使用。

二、题目内容二（难度：较难）

牛客每天有很多用户刷题，发帖，点赞，点踩等等，这些都会记录相应的积分。

有一个用户表(user)，简况如下：

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

第1行表示，user_id为1的用户积分增加了3分。

第2行表示，user_id为2的用户积分增加了3分。

第3行表示，user_id为1的用户积分又增加了1分。

.......

最后1行表示，user_id为3的用户积分增加了1分。

编写要求：

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

解释：user_id为1和3的2个人，积分都为4，都要输出

解题思路：

解题代码：

select id,name,grade_sum
from (
    select user_id,grade_sum,dense_rank()over(order by grade_sum desc) as rnk
    from (
        select distinct user_id,sum(grade_num)over(partition by user_id) as grade_sum
        from grade_info
        order by grade_sum desc
    ) as table1
) as table2 left join user
on table2.user_id=user.id
where rnk=1
order by id;

比较总结：

此题相比于第一道题的不同之处在于最高积分的用户可能有多个，因此不能直接用关键字limit筛选出来，这也是limit的局限之处
因此，需要使用dense_rank函数进行排名标号在筛选出来，此处用rank函数也可以，二者的不同之处可以自寻查找资料学习
此题除了多表连接以外，还有一个要点就是查询的嵌套，虽然嵌套可以是查询循序渐进，补充表内信息，但容易混乱，代码不宜解读，慎用！

此题的另一解题思路（巧用where等值比较）：

with tmp_table as (
    select t1.id, t1.name, sum(t2.grade_num) as grade_sum
    from user as t1 join grade_info as t2
    on t1.id = t2.user_id
    group by t1.id
)
select id, name, grade_sum
from tmp_table
where grade_sum = (select max(grade_sum) from tmp_table)
order by id;

三、题目内容三（难度：困难）

牛客每天有很多用户刷题，发帖，点赞，点踩等等，这些都会记录相应的积分。

有一个用户表(user)，简况如下：

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

第1行表示，user_id为1的用户积分增加了3分。

第2行表示，user_id为2的用户积分增加了3分。

第3行表示，user_id为1的用户积分减少了1分。

.......

最后1行表示，user_id为3的用户积分减少了1分。

编写要求：

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

解释：user_id为1和3的先加了3分，但是后面又减了1分，他们2个是2分，其他3个都是3分，所以输出其他三个的数据

解题思路：

解题代码：

select id,name,grade_sum
from(
    select *,dense_rank()over(order by grade_sum desc) as rnk
    from (
        select distinct user_id,
        sum(if(type='reduce',-grade_num,grade_num))over(partition by user_id) as grade_sum
        from grade_info
    ) as table1
) as table2 left join user
on user.id=table2.user_id
where rnk=1
order by id;;

比较总结：