## 一个双重not exists的sql问题

iman123 发布于 2014/07/02 08:43

```mysql> select * from Student;
+-----+-------+--------+-------+------+
| Sno | Sname | Ssex   | Sdept | Sage |
+-----+-------+--------+-------+------+
|   1 | name1 | male   | info  |   18 |
|   2 | name2 | female | info  |   19 |
|   3 | name3 | male   | info  |   20 |
+-----+-------+--------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from Course;
+-----+------------+------+---------+
| Cno | Cname      | Cpno | Ccredit |
+-----+------------+------+---------+
|   1 | computer   | NULL |       3 |
|   2 | programing |    1 |       2 |
|   3 | math       | NULL |       3 |
+-----+------------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from SC;
+-----+-----+-------+
| Sno | Cno | Grade |
+-----+-----+-------+
|   1 |   1 |    78 |
|   1 |   2 |    80 |
|   2 |   1 |    90 |
|   2 |   2 |    85 |
|   2 |   3 |    80 |
|   3 |   2 |    60 |
+-----+-----+-------+
6 rows in set (0.00 sec)```

```select Sname from Student s
where not exists (
select * from Course c
where not exists(
select * from SC
where Sno=s.Sno and Cno=c.Cno
)
);```

### 以下是问题补充：

@iman123：附上一些测试的数据 CREATE TABLE `Student` ( `Sno` int(11) NOT NULL, `Sname` varchar(255) DEFAULT NULL, `Ssex` varchar(255) DEFAULT NULL, `Sdept` varchar(255) DEFAULT NULL, `Sage` int(11) DEFAULT NULL, PRIMARY KEY (`Sno`) ); INSERT INTO `Student` VALUES (1,'name1','male','info',18),(2,'name2','female','info',19),(3,'name3','male','info',20); CREATE TABLE `Course` ( `Cno` int(11) NOT NULL, `Cname` varchar(255) DEFAULT NULL, `Cpno` int(11) DEFAULT NULL, `Ccredit` int(11) DEFAULT NULL, PRIMARY KEY (`Cno`) ); INSERT INTO `Course` VALUES (1,'computer',NULL,3),(2,'programing',1,2),(3,'math',NULL,3); CREATE TABLE `SC` ( `Sno` int(11) NOT NULL, `Cno` int(11) NOT NULL, `Grade` int(11) DEFAULT NULL, PRIMARY KEY (`Sno`,`Cno`) ); INSERT INTO `SC` VALUES (1,1,78),(1,2,80),(2,1,90),(2,2,85),(2,3,80),(3,2,60); (2014/07/02 08:56)
@iman123：也可以查看 http://sqlfiddle.com/#!2/d8dcb/2 (2014/07/02 08:57)

0
select  s.Sname from  Student s where s.Sno in (select  t.Sno from SC t group by t.Sno having count(t.Cno) =  (select count(c.Cno) from Course c) ;
select s.Sname from Student s where s.Sno in (select t.Sno from SC t group by t.Sno having count(t.Cno) = (select count(c.Cno) from Course c)); 嗯，少了个括号。