使用group by进行分组查询只能获取分组列以及聚合函数列,如果想要获取表中非分组列则获取不到,此时可以使用row_number()对分组进行排序,同时也可以获取非分组列。
表结构
学生课程分数表,三列
create table student_score
(
student_id int, --学生id
subject string, -- 课程
score int -- 分数
);
数据
insert into student_score(student_id, subject, score) values
(1, '语文’, 77),(1, '数学’, 80),(1, '英语’, 83),
(2, '语文’, 81),(2, '数学’, 90),(2, '英语’, 95),
(3, '语文’, 85),(3, '数学’, 83),(3, '英语’, 78),
(4, '语文’, 75),(4, '数学’, 96),(4, '英语’, 81);
分组排序
找出每门课分数最高的学生及分数
select student_id, subject, score
from
(
select student_id, subject, score,
row_number() over(partition by subject order by score desc) as row_num
from student_score
)tmp where row_num =1;
结果
3,'语文’,85
4,'数学’,96
2,'英语’,95