查询和01号学生学习的课程完全相同的其他同学的信息

查询所用到的各表表结构见此博客：https://blog.csdn.net/qq_41080850/article/details/84593860

查询和01号同学学习的课程完全相同的其他同学的信息：

代码：

select * from student where sid in 
(select sid from grade2 where sid not in
(select sid from grade2 where cid not in (select cid from grade2 where sid='01'))
group by sid 
having count(*)=(select count(*) from grade2 where sid='01') and sid != '01');

代码解析：

首先重新创建一个成绩表garde2用于测试上述代码，grade2表和student表的表结构均保持不变。

# 创建成绩表grade2：
create table grade2(
sid varchar(10),
cid varchar(10),
score decimal(4,1)
)engine myisam charset utf8;


# 向成绩表grade2中插入数据：
insert into grade2 values
('01','01',80),
('01','02',90),
('02','01',70),
('02','02',60),
('02','03',80),
('02','04',85),
('03','01',80),
('03','02',80),
('03','03',80),
('03','04',61),
('04','01',75),
('04','02',30),
('05','01',76),
('05','02',87),
('05','03',99),
('05','04',75),
('06','01',31),
('06','02',65),
('06','03',34),
('07','02',89),
('07','03',98),
('09','02',82),
('10','01',88),
('10','02',90),
('13','01',59),
('13','02',79),
('13','04',81);

查看grade2表的详细信息：

select * from grade2;

由上图可知，和01号学生学习的课程完全相同的其他学生的学生编号sid包括04和10；

下面从最内层开始逐步解析前文的查询代码：

查询出01号学生选修的所有课程的编号：

select cid from grade2 where sid='01';

查询出选修了01号学生没有选修课程的学生编号：

select sid from grade2 where cid not in (select cid from grade2 where sid='01');

上述查询结果中有重复的sid，这是因为有的学生选修的01号学生没选修课程不只一门。可以使用distinct关键字对sid进行去重处理。

查询选修的课程是01号学生选修课程的子集的学生编号：

select sid from grade2 where sid not in
(select sid from grade2 where cid not in (select cid from grade2 where sid='01'));

从上述查询结果中，筛选出选修的课程数量与01号学生选修的课程数量相等的其他学生的编号：

select sid from grade2 where sid not in
(select sid from grade2 where cid not in (select cid from grade2 where sid='01'))
group by sid 
having count(*)=(select count(*) from grade2 where sid='01') and sid != '01';

以上述查询结果为筛选条件，从student表中查询出与01号学生学习的课程完全相同的其他学生的信息：

select * from student where sid in 
(select sid from grade2 where sid not in
(select sid from grade2 where cid not in (select cid from grade2 where sid='01'))
group by sid 
having count(*)=(select count(*) from grade2 where sid='01') and sid != '01');

小结：

看到题目后想了很久，最初的想法是直接找出和" 01 "号的同学学习的课程完全相同的其他同学的编号，但没写出来能实现这种效果的SQL语句。最后在网上找到了一篇博客，前面代码的基本逻辑即来源于此：https://www.cnblogs.com/fengyunlishi/archive/2012/10/18/2730051.html

下面是我从代码中总结出的思路：

01号之外的其他学生可以分成两个大类，一类是选修了01号学生没有选修的课程的学生，另一类学生选修的课程是01号学生选修的课程的子集。采用逆向思维，可以先找出选修了01号学生没选课程的学生编号，然后以01号学生选修的课程数量为筛选条件，从剩下的选修的课程是01号学生选修的课程的子集这类学生中筛选出与01号学生所选课程完全相同的学生编号，此编号包含了01，以剔除了01之后的编号为筛选条件，从student表中选出和01号同学学习的课程完全相同的其他同学的信息。

另：下面评论中的朕水朋友提供了另一种很巧妙的查询思想：这是一种正向查询的思想，先筛选出其他学生中选修的课程数量和01号学生选修的课程数量相同的学生的学生编号和所修课程编号，然后将筛选结果与01号学生选修的课程编号进行内连接，然后再根据01号学生选修课程的数量做最后的筛选。

查询代码：

select * from student where sid in
(select sid from
(select sid,cid from grade2 where sid in
(select sid from grade2 where sid !='01' group by sid
having count(*)=(select count(*) from grade2 where sid='01' group by sid))) as t1
inner join (select cid from grade2 where sid='01') as t2
on t1.cid = t2.cid
group by t1.sid 
having count(*)=(select count(*) from grade2 where sid='01')
)