练习9：逆向思维，多重子查询

最新推荐文章于 2023-05-22 17:39:19 发布

原创最新推荐文章于 2023-05-22 17:39:19 发布 · 201 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

MySQL练习经典45题专栏收录该内容

28 篇文章

订阅专栏

本文介绍了一种查询与特定学生（如‘01’号同学）学习课程完全相同的学生信息的方法。通过逆向思维，首先确定学生表和成绩表，然后逐步筛选出既没有学习目标学生之外的课程，且所学课程数量也完全一致的其他学生。

9. 查询和“01”号同学学习的课程完全相同的其他同学的信息

关键词: “学生信息、课程、完全相同”
直接查不太好查，所以逆向思维，这里的“完全相同”有2层含义：

没有学习“01”号同学学习课程以外的其他课程
与“01”同学学习课程数量完全相等

第一步： 锁定表，学生表、成绩表
在这里插入图片描述
第二步： 找出学了“01”号同学学习课程以外其他课程的同学

SELECT Sid FROM sc WHERE Cid NOT IN (SELECT DISTINCT Cid FROM sc WHERE Sid = 01);

在这里插入图片描述
结果为空，因为01号同学学了所有课程，所以学了其以外课程的同学是空

第三步： 从所有学生中排除第二步找到的同学以及“01”号同学自身

SELECT  Sid FROM Sc 
WHERE Sid NOT IN
	(SELECT Sid FROM sc WHERE Cid NOT IN (SELECT DISTINCT Cid FROM sc WHERE Sid = 01))
	 AND Sid <> 01;

在这里插入图片描述
第四步： 筛选出与“01”号同学所学课程数量相等的同学

SELECT  Sid FROM Sc 
WHERE Sid NOT IN
	(SELECT Sid FROM sc WHERE Cid NOT IN (SELECT DISTINCT Cid FROM sc WHERE Sid = 01)) 
	AND Sid <> 01 
GROUP BY Sid 
HAVING COUNT(Cid) = (SELECT COUNT(DISTINCT Cid) FROM sc WHERE Sid = 01);

在这里插入图片描述
第五步：关联学生表，获取学生信息

SELECT * FROM student WHERE Sid IN
	(SELECT  Sid FROM Sc 
	WHERE Sid NOT IN
		(SELECT Sid FROM sc WHERE Cid NOT IN (SELECT DISTINCT Cid FROM sc WHERE Sid = 01)) 
		AND Sid <> 01 
	GROUP BY Sid 
	HAVING COUNT(Cid) = (SELECT COUNT(DISTINCT Cid) FROM sc WHERE Sid = 01));