一个双重not exists的sql问题

iman123 发布于 2014/07/02 08:43
阅读 183
收藏 0

如下图所示的三张表

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
Mervynly
Mervynly
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) ;
Mervynly
Mervynly
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)); 嗯,少了个括号。
返回顶部
顶部