今天这个帖子主要解决如何用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;