1 create table t_class2( 2 name varchar2(255), 3 subject varchar2(255), 4 score integer, 5 stuid integer 6 ) 7 8 insert into t_class2 values('张三','数学',89,1); 9 insert into t_class2 values('张三','语文',80,1); 10 insert into t_class2 values('张三','英语',70,1); 11 12 13 insert into t_class2 values('李四','数学',90,2); 14 insert into t_class2 values('李四','语文',70,2); 15 insert into t_class2 values('李四','英语',80,2); 16 17 select * from t_class2 18 19 20 --计算每个人的总成绩并排名 desc降序排列 sum()聚合函数求总 21 select name, sum(score) sumscore from t_class2 group by name order by sumscore desc 22 23 24 --计算每一个人的平均成绩 avg()聚合函数求平均值 25 select name, avg(score) avgscore from t_class2 group by name 26 27 28 --计算每个人的单科最高成绩 max()聚合函数求最大值 29 select name,max(score) maxscore from t_class2 group by name 30 31 32 --列出每一科成绩最好的学生 33 34 select c2.name, c2.subject,c2.score from t_class2 c2 , 35 ( select subject, max(score) maxscore from t_class2 group by subject) c1 36 where c2.subject=c1.subject and c2.score=c1.maxscore