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

我第一次写的很繁琐。先用聚合函数统计每一个user_id的grade_num，然后用窗口函数排序，最后用where语句筛选。

with summary as (
    select
        u.id, 
        u.name, 
        sum(g.grade_num) as grade_num
    from user u
    join grade_info g
        on u.id = g.user_id
    group by 1, 2 -- 选择的列中非聚合的列都要出现在group by 里。
    ),

ranking as (select 
    id,
    name,
    grade_num,
    rank() over(
        order by grade_num desc
    ) as rk
from summary)

select 
    id, 
    name,
    grade_num
from ranking 
where rk = 1
order by id asc;

一种更简单的写法是，在一个select语句中同时处理聚合和排序。

子查询内部的执行顺序

SQL 引擎在处理这个子查询时，遵循以下逻辑步骤：

1. FROM & JOIN：先将 user 表和 grade_info 表根据 id 连接起来，形成一张包含所有明细行的大表。
2. GROUP BY：这是关键的“压缩”时刻。它把具有相同 id 和 name 的多行记录合并成一行。
3. SUM(g.grade_num)：在压缩的同时，计算每个分组内的积分总和。
4. 窗口函数 DENSE_RANK() OVER(...)：注意，窗口函数是在 GROUP BY 完成之后才执行的。此时，窗口函数面对的不再是原始明细表，而是已经被 GROUP BY 压缩后的汇总表。它会对这些“压缩”后的行，根据 SUM 出来的总分进行排名。

3. 通过 HAVING 直接过滤出总分等于“全表最高分”的用户。子查询内部只负责找出那个最高分数值。

select
    u.id,
    u.name,
    sum(g.grade_num) as grade_num
from user u
join grade_info g
on u.id = g.user_id
group by 1, 2
having grade_num  = (
    select sum(grade_num)
    from grade_info
    group by user_id
    order by sum(grade_num) desc
    limit 1
)