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

题目描述

我们有两个表：

• user：包含用户信息，包括用户ID id 和用户名 name。
• grade_info：包含积分信息，包括用户ID user_id、积分数量 grade_num 和积分类型 type。

目标是查询出积分增加最高的用户的ID、名字及其总积分，并按用户ID升序排列。

知识点

• 窗口函数：使用SUM和DENSE_RANK窗口函数计算每个用户的总积分和排名。
• 连接操作：使用JOIN连接用户表和积分表。
• 排序：使用ORDER BY子句按用户ID升序排列结果。

关键问题分析

1. 计算每个用户的总积分和排名

我们使用SUM窗口函数计算每个用户的总积分，并使用DENSE_RANK函数按总积分降序排名：

select user_id, sum(grade_num) as grade_num,
       dense_rank() over(order by sum(grade_num) desc) as rk
from grade_info
where type = 'add'
group by user_id

• sum(grade_num) as grade_num: 计算每个用户的总积分。
• dense_rank() over(order by sum(grade_num) desc) as rk: 按总积分降序排名。
• where type = 'add'：确定积分是增加而不是减少。
• group by user_id：按照用户id分组。

2. 连接用户信息

我们使用JOIN连接用户表和积分表，以获取用户名：

join user on user.id = sub1.user_id

• join user on user.id = sub1.user_id: 连接用户信息。

3. 筛选积分最高的用户

我们通过WHERE子句筛选出积分最高的用户：

where rk = 1

• where rk = 1: 筛选出积分最高的用户。

4. 排序输出

我们按用户ID升序排列输出结果：

order by user.id asc

• order by user.id asc: 按用户ID升序排列。

完整代码

select user.id, user.name, sub1.grade_num
from (
    select user_id, sum(grade_num) as grade_num,
           dense_rank() over(order by sum(grade_num) desc) as rk
    from grade_info
    where type = 'add'
    group by user_id
) sub1
join user on user.id = sub1.user_id
where rk = 1
order by user.id asc;