sql分组topN

最新推荐文章于 2024-09-30 15:50:05 发布

LCLDeer

最新推荐文章于 2024-09-30 15:50:05 发布

阅读量56

点赞数

文章标签： sql 数据库 hive mysql

本文链接：https://blog.csdn.net/feiyuxc/article/details/132827226

版权

文章讲述了如何从高三三个班级的学生成绩表中，利用SQL的窗口函数ROW_NUMBER(),rank(),dense_rank()来获取每个班级总分前两名学生的姓名、班级、分数，并处理排名问题。测试实例展示了如何筛选出排名小于3的学生信息，以及rank()和dense_rank()在处理分数相同情况下的区别。

摘要由CSDN通过智能技术生成

需求：取高三3个班总分前2名学生的姓名、班级、分数和名次。
数据信息：

name class score
刘备 1 99
赵云 1 85
马超 1 92
典韦 2 99
许褚 2 88
于禁 2 95
吕布 3 100
潘凤 3 60
张辽 3 89

需求结果：

name class score num
刘备 1 99 1
马超 1 92 2
典韦 2 99 1
于禁 2 95 2
张辽 3 89 1
潘凤 3 60 2

测试实例：
分析需求，高三三个班，每班取总分前2名，需要字段姓名、班级、分数都能在表中直接取，缺失排名信息。
不考虑分数相同情况，则每班各取2名同学信息。
使用窗口函数给学生总分打上排名标记

SELECT  name
        ,class
        ,score
        ,ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC ) num
FROM    stu_test
;

name class score num
刘备 1 99 1
马超 1 92 2
赵云 1 85 3
典韦 2 99 1
于禁 2 95 2
许褚 2 88 3
张辽 3 89 1
潘凤 3 60 2
吕布 3 100 3

比目标结果多了排名为3的信息，加上限制条件，排名小于3即可。

SELECT *
from (
SELECT  name
        ,class
        ,score
        ,ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC ) num
FROM    stu_test
)temp 
where num < 3
;

name class score num
刘备 1 99 1
马超 1 92 2
典韦 2 99 1
于禁 2 95 2
张辽 3 89 1
潘凤 3 60 2

如果需考虑分数相同情况，可将row_number()函数替换为rank()或dense_rank()函数。
函数特性：
**rank()**相同分数排名相同，下一名名次跳跃为总人数名次，如三人并列第1，排名1 1 1，再下一个名次不是排名2，而是排名第4，即排名是1 1 1 4；
**dense_rank()**相同分数排名相同，下一名名次不跳跃，如三人并列第1，排名1 1 1，再下一个名次排名2，即排名是1 1 1 2；