如何用Oracle窗口函数解决sql select不能显示除group by后面的列的问题

最新推荐文章于 2024-03-14 14:04:29 发布

原创最新推荐文章于 2024-03-14 14:04:29 发布 · 838 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #oracle #数据库

本文通过一个具体的例子介绍了如何利用Oracle窗口函数解决SQL查询中无法直接显示非GROUP BY后的列的问题。例如，在一个包含年级、班级、学生姓名和分数的数据表中，如何找出每个年级的最高分学生及其所在班级和姓名。传统的GROUP BY方法会导致'ORA-00979: not a GROUP BY expression'错误。通过使用窗口函数row_number() over (partition by ... order by ...)，可以正确地获取年级第一名学生的完整信息。

今天这个帖子主要解决如何用Oracle窗口函数解决sql select不能显示除group by后面的列的问题，譬如某个学校初一年级一共三个班级，每个班级有不同的学生人数，想要筛选出年纪第一，并且select出该学生所属班级，姓名等group by没有聚合到的列名。
老规矩，用sqlfiddle建数据模型。

create table student(grade char(10),classno char(10),student_name varchar2(20),score number);
insert into student values('junior','A','joy',100);
insert into student values('junior','B','EZO',80);
insert into student values('junior','B','Mark',60);

select * from student;

在这里插入图片描述
首先，如果只是要筛选出这个学校初一年级期末考试最高分。怎么写这个sql？
答案如下：

select max(score),grade from student group by grade;

在这里插入图片描述
问题来了，如何要让这条记录的其他字段信息也随着筛选出现呢？譬如，年纪第一名来自哪个班级？学生名？

select max(score),grade,classno,student_name from student group by grade;

这样写就会导致错误’ORA-00979: not a GROUP BY expression
‘，因为classno，student_name 并没有写到group by里面去。
试着用下窗口函数：

select * from (
select grade,classno,student_name, score,row_number() over(partition by grade order by score desc) as rank from student
) t1 where t1.rank=1;