有个学生成绩表如下,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
解决方案 »
- 商品订单的数据库建表
- delete from user a , log b where 错误
- mysql 如何用SQL返回一个值,并用这个值进行查询???
- mysql如何启动 、查看、binlog
- 问一个Sql语句
- mysql下怎么使用REPLACE...SELECT..,语法是什么啊?另外
- 求教关于数据库中的数据匹配问题
- 关于or 查询语句的默认值
- 关于存储大量数据的问题
- mysql 批量replace into死锁
- 急!!!!!!!!!安装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));