用MySQL求如何揪出挂科2门以上的学生的名字

Question

如图是表的基本结构，具体建表信息我也提供一下：

CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) default CHARSET=utf8;

insert  into `class`(`cid`,`caption`) values (1,'三年二班'),(2,'三年三班'),(3,'一年二班')
,(4,'二年九班');

CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`)
) default CHARSET=utf8;

insert  into `course`(`cid`,`cname`,`teacher_id`) values (1,'生物',1),(2,'物理',2),(3,'体育',3)
,(4,'美术',2);

CREATE TABLE `score1` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`)
)default CHARSET=utf8;


insert  into `score1`(`sid`,`student_id`,`course_id`,`num`) values (1,1,1,10),(2,1,2,9),
(5,1,4,66),(6,2,1,8),(8,2,3,68),(9,2,4,99),(10,3,1,77),(11,3,2,66),(12,3,3,87),(13,3,4,99),
(14,4,1,79),(15,4,2,11),(16,4,3,67),(17,4,4,100),(18,5,1,79),(19,5,2,11),
(20,5,3,67),(21,5,4,100),(22,6,1,9),(23,6,2,100)
,(24,6,3,67),(25,6,4,100),(26,7,1,9),(27,7,2,100),(28,7,3,67)
,(29,7,4,88),(30,8,1,9),(31,8,2,100),(32,8,3,67),
(33,8,4,88),(34,9,1,91),(35,9,2,88),(36,9,3,67),(37,9,4,22)
,(38,10,1,90),(39,10,2,77),(40,10,3,43),(41,10,4,87)
,(42,11,1,90),(43,11,2,77),(44,11,3,43),(45,11,4,87),
(46,12,1,90),(47,12,2,77),(48,12,3,43),(49,12,4,87),(52,13,3,87);



CREATE TABLE `student1` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
)default CHARSET=utf8;

insert  into `student1`(`sid`,`gender`,`class_id`,`sname`) values (1,'男',1,'理解'),
(2,'女',1,'钢蛋'),(3,'男',1,'张三'),(4,'男',1,'张一'),(5,'女',1,'张二'),(6,'男',1,'张四'
),(7,'女',2,'铁锤'),
(8,'男',2,'李三'),(9,'男',2,'李一'),(10,'女',2,'李二'),(11,'男',2,'李四')
,(12,'女',3,'如花'),(13,'男',3,'刘三'),(14,'男',3,'刘一'),(15,'女',3,'刘二'),(16,'男',3,'刘四');

CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) default CHARSET=utf8;

insert  into `teacher`(`tid`,`tname`) values (1,'张磊老师'),(2,'李平老师')
,(3,'刘海燕老师'),(4,'朱云海老师'),(5,'李杰老师');

题目是：查询挂科超过两门(包括两门)的学生姓名和班级。
按正常逻辑来说，缺考了，成绩不存在或者null的，自然是不及格的。一开始我想法：简单！把不及格的揪出来。后来发现不对，要统计不及格成绩大于2门的不好做，因为有缺考的！因此我解题思路就是先找出及格3门课以上的同学，然后排除法。
代码如下：

SELECT sname from student1 WHERE sid not in(SELECT c.student_id FROM student1 a left join score1 c on a.sid=c.student_id WHERE num>60 GROUP BY sname HAVING count(num)>2);

我感觉这样写有2个select，有没有办法再简化到一个select？

Answer 1

已经写很好了，如果我写，我可能会写成正向思维的，不会更简单，但是也比较好理解：

SELECT a.sname  from (
SELECT sscc.sid ,sscc.sname, sscc.cname, (CASE WHEN sc.num is NULL THEN 0 ELSE sc.num END) nnum from 
(SELECT * from student1 s, course c) sscc LEFT JOIN score1 sc
on sscc.sid = sc.student_id and sc.course_id = sscc.cid
) a
where nnum < 60
GROUP BY a.sname HAVING count(nnum) >= 2
ORDER BY a.sid

就是把学生和学科表合为一个表，然后去向外关联，这样就不会用到in了，不知道是否会比你的快。仅供参考。