oracle语句中的聚合函数以及分组group by的使用实例

 

create table t_class2(
  name varchar2(255), 
  subject varchar2(255), 
  score integer, 
  stuid integer
)

insert into t_class2 values('张三','数学',89,1);
insert into t_class2 values('张三','语文',80,1);
insert into t_class2 values('张三','英语',70,1);


insert into t_class2 values('李四','数学',90,2);
insert into t_class2 values('李四','语文',70,2);
insert into t_class2 values('李四','英语',80,2);

select * from t_class2


--计算每个人的总成绩并排名  desc降序排列  sum()聚合函数求总
select name, sum(score) sumscore  from t_class2 group by name order by sumscore desc


--计算每一个人的平均成绩 avg()聚合函数求平均值
select name, avg(score) avgscore  from t_class2 group by name


--计算每个人的单科最高成绩 max()聚合函数求最大值
select name,max(score) maxscore from t_class2 group by name


--列出每一科成绩最好的学生

select c2.name, c2.subject,c2.score from t_class2 c2  ,
( select subject,  max(score) maxscore from t_class2 group by subject) c1
where c2.subject=c1.subject and c2.score=c1.maxscore