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

参考

参考博客

建表

学生表

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

科目表

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

成绩表

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

教师表

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

题目

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

我的答案

	SELECT * from student where sid IN(
		SELECT r2.sid from (
				(SELECT sid,cid from sc where sid="01") AS r1 
			INNER JOIN (
				SELECT sid,cid from sc where sid IN (
						SELECT sid from sc 
						GROUP BY sid 
						HAVING sid!="01" 
						AND COUNT(cid)=(SELECT COUNT(cid) from sc where sid="01")))
				AS r2
			ON r1.cid=r2.cid
		)	GROUP BY r2.sid 
			HAVING COUNT(r2.sid)=(SELECT COUNT(cid) from sc where sid="01"))

解析

1.先查询01同学的sid和所学的课程编号cid（我的答案中第3行）

SELECT sid,cid from sc where sid="01"

查询结果：

2.过滤掉课程总数不一样的同学，查询所学课程总数与01同学所学课程总数一样的同学的sid（我的答案中6-9行），

SELECT sid from sc 
GROUP BY sid 
HAVING sid!="01" 
AND COUNT(cid)=(SELECT COUNT(cid) from sc where sid="01")

查询结果：

3.根据上面得到的sid查询这些同学的所有所学课程的cid（我的答案中5-9行）

SELECT sid,cid 
from sc 
where sid IN (
	/*引用第二步的sql*/
	SELECT sid from sc 
	GROUP BY sid 
	HAVING sid!="01" 
	AND COUNT(cid)=(SELECT COUNT(cid) from sc where sid="01"))

查询结果：

4.使用内连接inner join将第1步的结果和第3步的结果通过cid进行连接（我的答案中2-11行）

//第一行sql中为了方便展示结果 将r2.sid换成了*号
//假如出现1同学有A和B课程，2同学有A，B和C课程，二者是不会进行匹配的，因为在第二步的时候已经对课程数量不同的同学进行过滤。
SELECT * from (
		(SELECT sid,cid from sc where sid="01") AS r1 
	INNER JOIN (
		SELECT sid,cid from sc where sid IN (
		SELECT sid from sc 
		GROUP BY sid 
		HAVING sid!="01" 
		AND COUNT(cid)=(SELECT COUNT(cid) from sc where sid="01")
		)
	) AS r2
	ON r1.cid=r2.cid
)

查询结果：

5.对第四步结果根据其他同学的sid进行分组(第四步查询结果中的sid(1)字段)，并再次对课程数量进行过滤（是否和01同学的课程数量一致）

SELECT * from (
		(SELECT sid,cid from sc where sid="01") AS r1 
	INNER JOIN (
		SELECT sid,cid from sc where sid IN (
		SELECT sid from sc 
		GROUP BY sid 
		HAVING sid!="01" 
		AND COUNT(cid)=(SELECT COUNT(cid) from sc where sid="01")
		)
	) AS r2
	ON r1.cid=r2.cid
) GROUP BY r2.sid 
HAVING COUNT(r2.sid)=(SELECT COUNT(cid) from sc where sid="01")

查询结果：

从上面结果可以看出 只有一条sid为07的结果已被过滤掉，已经得到我们想要的同学的sid为04和06

6.得到想要的sid，直接去student表中根据sid进行查询即可

//这里第二行的r2.sid也就是我们最终需要的sid，上面第4和第5步为了方便看结果使用的是*号
SELECT * from student where sid IN(
	SELECT r2.sid from (
			(SELECT sid,cid from sc where sid="01") AS r1 
		INNER JOIN (
			SELECT sid,cid from sc where sid IN (
			SELECT sid from sc 
			GROUP BY sid 
			HAVING sid!="01" 
			AND COUNT(cid)=(SELECT COUNT(cid) from sc where sid="01")
			)
		) AS r2
		ON r1.cid=r2.cid
	) GROUP BY r2.sid 
	HAVING COUNT(r2.sid)=(SELECT COUNT(cid) from sc where sid="01")
)

查询结果：

---

题外话：
这道题是昨天在一个帖子上看的，作者也没给出答案，我以前做的都是比较简单的查询，也很少去练sql，遇到这种也没什么思路，就去百度了，找了个博主的帖子去看，参考博主的解题思路，自己也去一点一点的试，中间一次我一度以为自己成功了，而且对比博主的sql更短，结果我去表中改了改数据，又不是我想要的结果了，经过长时间的修改最终还是达到了效果，最终对比博主的sql发现居然是差不多的。
参考博客上面已经给出，有什么问题的可以去看看，最后如果对sql有疑问欢迎提出，一起讨论。