sql从学生成绩表中查出各科成绩分布最多的记录

题目：有一个数据表student，包含name（姓名），dept（科目），score（成绩）三个字段，现在要求查出每科成绩分数分布最多的项，如语文成绩80分最多，就输出语文80分的人数，数学90分的人最多，输出数学90分的人数；

测试数据：

insert into student values ("deng","80","语文");

insert into student values ("zhang","80","语文");

insert into student values ("li","70","语文");

insert into student values ("xiong","85","语文");

insert into student values ("bao","60","语文");

insert into student values ("king","80","语文");

insert into student values ("ying","90","语文");

insert into student values ("hong","60","语文");

 

insert into student values ("deng","90","数学");

insert into student values ("zhang","80","数学");

insert into student values ("li","80","数学");

insert into student values ("xiong","65","数学");

insert into student values ("bao","60","数学");

insert into student values ("king","80","数学");

insert into student values ("ying","90","数学");

insert into student values ("hong","60","数学");

 

insert into student values ("deng","70","英语");

insert into student values ("zhang","85","英语");

insert into student values ("li","85","英语");

insert into student values ("xiong","65","英语");

insert into student values ("bao","60","英语");

insert into student values ("king","85","英语");

insert into student values ("ying","70","英语");

insert into student values ("hong","65","英语");

 

第一步：创建视图view2： 得到各科成绩分布的记录数目；并按照科目，记录数大小倒叙排列；

create view view2 as select dept,score,count(score) as countnum from student group by dept,score order by dept,countnum desc;

第二步：创建视图view3： 得到所有的科目列表；

create view view3 as select dept from student group by dept;

 

第三步：左连接查询：  得到各科成绩第一条记录数；

select view3.dept,view2.score,view2.countnum from view3 left join view2 on view3.dept=view2.dept group by view3.dept;

 

 

 

 

 其中第二步和第三步可以合并优化

select * from view2 group by dept；可以直接得到最终结果；

或者是：

select dept,score,max(countnum) from view2 group by dept;

 

 

有一个问题，就是有某一科有两个成绩分布记录一样，这种情况；

第一步：再次创建一个试图简化问题：create view view4 as select dept,max(countnum) as maxcount from view2 group by dept;

 

 

 

第二步：查找：  select view2.dept,view2.score,view2.countnum from view2,view4 where  view2.dept=view4.dept and view2.countnum =view4.maxcount