SQL查询——查询和和xxx同学所选课程完全一样的同学

前言

今天碰到一个sql问题，想了半天才想出来，为了记录，所以写了这篇博客。
题目和环境是这样的
有四张表：学生表、教师表、课程表、成绩表
学生表(student)：编号(sno)、姓名(sname)、年龄(sage)、性别(ssex)
教师表(teacher)：编号(tno)、姓名(tname)
课程表(course)：编号(cno)、课程名(cname)、教师编号(tno)
成绩表(sc)：学生编号(sno)、课程编号(cno)、分数(score)

问题是：查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名。

这题的难点是：s001号同学所选的课程不止一门，所以查询出了s001号同学所选的所有课程，无法通过等于来限定条件进行查询。也无法通过in来限定，因为in是等于范围内的某个，从某种意义上来说，和等于效果是一样的。

答案

冥思苦想良久，终于想出了解法，可能还有更好的答案，但我暂时只想到了这一种

思路

我们可以查出s001号同学所选的所有课程编号，和所选的课程总数，然后在查询出所有“选过s001同学所选课程”的记录，再按学号分组，统计条数是否相同。只看思路可能一时间看不懂，可以先往下看代码，我代码会一步一步的来写。

代码(mysql)

1. 查询s001同学所选的所有课程

SELECT cno FROM sc WHERE sno='s001';

2. 查询出s001号同学所选课程的总数

SELECT COUNT(cno) FROM sc GROUP BY sno HAVING sno='s001'; 

3. 所有“选过s001同学所选课程”的记录，并且排除s001同学的记录。这一步比较关键，要理解为什么要查这一步。这一步操作之后，临时表里的所有记录的课程，就都是s001同学选过的了。然后我就可以继续进行筛选了

SELECT * FROM sc WHERE cno IN 
(SELECT cno FROM sc WHERE sno='s001') AND sno<>'s001';

4. 将第三步查询出来的结果作为临时表，对临时表进行操作：按照学号进行分组，统计每位同学所选课程的总数，

SELECT temp.sno,COUNT(temp.cno) 
FROM (SELECT * FROM sc WHERE cno IN 
(SELECT cno FROM sc WHERE sno='s001') AND sno<>'s001') AS temp 
GROUP BY temp.sno

5. 在第四步的基础上进行筛选：条件是课程总数与s001同学相等

SELECT temp.sno,COUNT(temp.cno) 
FROM (SELECT * FROM sc WHERE cno IN 
(SELECT cno FROM sc WHERE sno='s001') AND sno<>'s001') AS temp 
GROUP BY temp.sno 
HAVING COUNT(temp.cno)=
(SELECT COUNT(cno) FROM sc GROUP BY sno HAVING sno='s001') ;

6. 到此已经大功告成了，最后再和student表进行连接查询，查出姓名即可

SELECT temp.sno,st.`sname` FROM 
(SELECT * FROM sc WHERE cno IN 
(SELECT cno FROM sc WHERE sno='s001') AND sno<>'s001') AS temp 
INNER JOIN student AS st ON st.`sno`=temp.sno
GROUP BY temp.sno HAVING COUNT(temp.cno)=
(SELECT COUNT(cno) FROM sc GROUP BY sno HAVING sno='s001');

经过验证，是OK的。

总结

其实我一开始，是想到用in去进行限定的，但是in根本不能完成，因为in是等于枚举里面的某一项即满足条件。

那我又想：sql里面有没有“等于某个范围里面所有值”这样的关键字，或者语法呢？但其实这种想法就是错误的，一个字段的值只可能是 一个，怎么可能会是多个呢，所以也不可能有这个语法。

最终采用了两组条件进行限制，然后查了出来。