有个学生成绩表如下,sid学号,cid课程号,score成绩
+-----+-----+-------+
| sid | cid | score |
+-----+-----+-------+
| 1 | 1 | 77 |
| 1 | 2 | 88 |
| 1 | 3 | 78 |
| 1 | 5 | 77 |
| 1 | 6 | 64 |
| 2 | 1 | 99 |
| 2 | 2 | 66 |
| 2 | 3 | 75 |
| 2 | 4 | 0 |
| 2 | 5 | 76 |
| 2 | 6 | 22 |
| 3 | 4 | 29 |
| 3 | 5 | 0 |
| 4 | 1 | 63 |
| 4 | 3 | 59 |
| 4 | 5 | 61 |
| 4 | 6 | 98 |
| 5 | 1 | 53 |
| 5 | 2 | 69 |
| 5 | 3 | 69 |
| 5 | 5 | 55 |
| 5 | 7 | 82 |
| 6 | 1 | 74 |
| 6 | 2 | 68 |
| 6 | 3 | 71 |
| 6 | 4 | 95 |
| 6 | 7 | 59 |
| 7 | 2 | 33 |
| 7 | 3 | 53 |
| 7 | 4 | 87 |
| 7 | 6 | 63 |
| 8 | 1 | 81 |
| 8 | 2 | 80 |
| 8 | 3 | 44 |
| 8 | 4 | 90 |
| 8 | 5 | 100 |
| 8 | 6 | 44 |
| 8 | 7 | 99 |
+-----+-----+-------+求一SQL,查询至少学过sid为3的同学所有课程的sid?
比如sid为3的同学学过课程4和课程5,那么查询结果应该是学号sid为2和8
+-----+-----+-------+
| sid | cid | score |
+-----+-----+-------+
| 1 | 1 | 77 |
| 1 | 2 | 88 |
| 1 | 3 | 78 |
| 1 | 5 | 77 |
| 1 | 6 | 64 |
| 2 | 1 | 99 |
| 2 | 2 | 66 |
| 2 | 3 | 75 |
| 2 | 4 | 0 |
| 2 | 5 | 76 |
| 2 | 6 | 22 |
| 3 | 4 | 29 |
| 3 | 5 | 0 |
| 4 | 1 | 63 |
| 4 | 3 | 59 |
| 4 | 5 | 61 |
| 4 | 6 | 98 |
| 5 | 1 | 53 |
| 5 | 2 | 69 |
| 5 | 3 | 69 |
| 5 | 5 | 55 |
| 5 | 7 | 82 |
| 6 | 1 | 74 |
| 6 | 2 | 68 |
| 6 | 3 | 71 |
| 6 | 4 | 95 |
| 6 | 7 | 59 |
| 7 | 2 | 33 |
| 7 | 3 | 53 |
| 7 | 4 | 87 |
| 7 | 6 | 63 |
| 8 | 1 | 81 |
| 8 | 2 | 80 |
| 8 | 3 | 44 |
| 8 | 4 | 90 |
| 8 | 5 | 100 |
| 8 | 6 | 44 |
| 8 | 7 | 99 |
+-----+-----+-------+求一SQL,查询至少学过sid为3的同学所有课程的sid?
比如sid为3的同学学过课程4和课程5,那么查询结果应该是学号sid为2和8
解决方案 »
- -- 一个奇怪的My SQL游标循环问题 --
- Linux下怎么让PDO支持mysql?
- 存储过程
- 连接优化
- 80分,MySQL中TIMESTAMP类型的数据到底要和什么类型的数据进行比较?
- 如何安全的配置和应用MySQL数据库?
- 有没有postgresql免安装版??
- mysql数量级是多少?
- VB6能读取MySQL的内容嘛?求助!
- 哈哈,我现在学会了PHP了,用PHP+MYSQL写了一个简单的论坛,大家快来帮我测试一下。
- 急!!!!!!!!!安装MySQL时无法启动服务(could not start the service MYSQL .Error:0)
- mysql官网中的下载连接为什么没有中国的
where cid in (select cid from tb where sid=3)
where cid in (select cid from tb where sid=3)楼上正解
from 有个学生成绩表 a
where not exists (select 1 from 有个学生成绩表 where sid=3 and cid not in (select cid from 有个学生成绩表 where sid=a.sid ))
+-----+
| sid |
+-----+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 8 |
+-----+
查询错误。我只想得到sid为2和8, 从表中也可以看出,只有sid为2和8的同学学过课程4和课程5
mysql> select distinct sid from sc a where not exists (select 1 from sc where si
d=3 and cid not in (select cid from sc where sid=a.sid));