一、下面是某班级学生考试成绩表、学生信息表,所有学生科目成绩均已录入,缺考计0分。
找出所有全部成绩合格(分数大于等于60分)的学生姓名
SELECT stu_name FROM stu_info as s left join grade_info as g ON s.stu_no=g.stu_no
WHERE grade>=60 GROUP BY stu_name HAVING COUNT(*)>=count(distinct sub);
找出总成绩最高的前三名同学姓名,以及他们分别的全科总分数
SELECT stu_name, SUM(grade) as 总分 FROM (SELECT stu_name, IFNULL(grade,0) grade FROM stu_info as s LEFT JOIN grade_info as g ON s.sno=g.sno) as b GROUP BY b.sname ORDER BY 总分 DESC LIMIT 3;
找出所有全部成绩合格(分数大于等于60分)的学生姓名 找出总成绩最高的前三名同学姓名 以及他们分别的全科总分数