SQL进阶：查询分组内Top n问题

整体阅读时间为15分钟左右

  我们经常会遇到查询每一组中的前几名（Top n）的场景，针对与这种场景，我们就需要用到窗口函数中的row_number()函数

1.场景分析

  现在有这么一张部门薪资表，我们需要查询查询每个部门中薪资最高的前两名。在这里我们首先想到的是group by进行分组，但是仔细思考我们会发现group by分组后我们只能对每组的数据做一些聚合求出每一组的最大值、最小值、平均值等，好像是没办法把每组中的数据散列出来，没办法的到每一组中的每一条记录。这个时候就需要用到窗口函数中的row_number()函数

2.row_number()函数语法

 row_number() over(partition by 组名xxx order by yyy)

• partition by 后面跟字段名，表示对那个字段进行分组
• order by 跟字段名，表示对组中哪个字段进行排序
• row_number()函数最后会为每组从1进行标号

3.具体实现

• 我们先使用row_number()函数对department进行分组，并对每组中的price字段进行降序排序，就可以得到以下结果

select 
    *,row_number() over(partition by department order by price desc) as rn 
from test

• 接着我们需要过滤出每组的前两名，首先我们想到的是having对rn进行<=2过滤，但是仔细思考我们会发现row_number()函数的位置是在select和from之间，它的执行顺序是在having之后的，所以我们无法使用having对其进行过滤。所以我们需要换一种思路，那就是将其查询结果作为一个中间表，对中间表进行数据过滤即可。于是我们就得到了每个部门中薪资最高的前两名

select *
from (
    select 
        *,row_number() over(partition by department order by price desc) as rn 
    from test
) as t 
where rn <= 2

4.总结

至此，我们可以总结出一个公式，帮助大家在遇到Top n问题时轻松应对