group by与partition by用法

--本文采用Oracle数据库测试，前4个查询为一组，后2个查询为一组，每组前面的查询是为了推出最后的查询

--创建表，为了简化处理，字段类型都采用varchar
create table tb_sc(
  uName varchar2(10),
  uCourse varchar2(10),
  uScore varchar2(10)
);

--插入数据
insert into tb_sc values('张三','语文','80');
insert into tb_sc values('张三','数学','95');
insert into tb_sc values('李四','语文','90');
insert into tb_sc values('李四','数学','70');
insert into tb_sc values('王五','语文','90');
insert into tb_sc values('王五','数学','90');

 

--查询所有结果如下

select * from tb_sc;

 

--①查询每名学生的平均分（展示姓名、平均分）
select uName,avg(uScore) from tb_sc group by uName;

--②查询每名学生的平均分并按降序排列（展示姓名、平均分）
select uName,avg(uScore) from tb_sc group by uName order by avg(uScore) desc;

--③查询平均分数高于85分的学生（展示姓名、平均分）
select uName,avg(uScore) from tb_sc  group by uname having avg(uScore)>'85'  order by avg(uscore) desc;

--④查询姓名不为张三且平均分数高于85分的学生（展示姓名、平均分）
select uName,avg(uScore) from tb_sc where uName!='张三' group by uname having avg(uScore)>'85'  order by avg(uscore) desc;

--⑤查询所有学生的信息并将每个学生的各科成绩降序排列
select t.*,row_number() over(partition by t.uname order by t.uscore desc) rm from tb_sc t;

 

--⑥查询每个学生考得最好的科目并展示该科目的成绩
select * from (
select t.*,row_number() over(partition by t.uname order by t.uscore desc) rm from tb_sc t
) where rm=1;

 

注："row_number() over(partition by 分组字段 order by 排序字段)"开窗函数通常用于查询所有分组并将各个窗体进行排序