SQL做题日志1||获得积分最多的人

Inochigohan
328 阅读4分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

🔖文章摘要

题目选自牛客网在线编程SQL实战，文章包含一组题，题目难度依次加深，内容依次为：①题目介绍、②代码编写要求、③解题思路、④解题代码和⑤要点总结共五大部分。

📝题目一（难度：简单）

📌题目介绍

牛客每天有很多用户刷题，发帖，点赞，点踩等等，这些都会记录相应的积分。有一个用户表(user)，还有一个积分表(grade_info)，第1行表示，user_id为1的用户积分增加了3分；第2行表示，user_id为2的用户积分增加了3分；第3行表示，user_id为1的用户积分又增加了1分.......最后1行表示，user_id为5的用户积分增加了3分。

image.png

📌编写要求

请写一个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分。

image.png

📌编写要求

请写一个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 temp_table as (
    select table1.id, table1.name, sum(table2.grade_num) as grade_sum
    from user as table1 join grade_info as table2
    on table1.id = table2.user_id
    group by table1.id
)
select id, name, grade_sum
from temp_table
where grade_sum = (select max(grade_sum) from temp_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分。

image.png

📌编写要求

请写一个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;;

📌比较总结

  • 这一道题的编写要求同第二道题一致，不同之处在于所给的数据中积分有了减少的类型，因此只需在第二道题的解题思路上稍作修改，「将类型为减少的数值改为对应的相反数即可」。
avatar
文章
阅读
粉丝