需求就是给你两张表
student_info表
course_info表
统计各个学院英语成绩的通过率百分比,只保留及格率大于10%的,并按照百分比排序
这个题目很弱鸡,面试时居然告诉我由于此题目较难应试者可以选做。。。
不多说了给答案吧
select s.major,count(1)/(select count(1) from student_info s2 where s2.major=s.major) as pass from student_info s join course_info c on s.id=c.student_id where c.score>=60 and c.course_name='英语' group by s.major having pass>0.1 order by pass asc