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;     有一个问题，就是有某一科有两个成绩分布记录一样，这种情况； select * from view2 where countnum in (select max(countnum) from view2 group by dept);