查询和01号学生选择课程完全一样的学生信息

数据表定义和题目来源：https://www.jianshu.com/p/476b52ee4f1b

查询和01号学生选择课程完全一样的学生信息

代码：

select SId,Sname,Sage,Ssex from Student where not exists( select SId,CId from SC a where a.SId='01' and not exists( select b.SId from SC b where Student.SId=b.SId and a.CId=b.CId ) )

所有数据:

学生表

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' , '女');

SC表

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);

由题目和上面给出的数据可知，01号同学选了课程号为01，02，03的课程，而与01号同学选课完全相同的有01，02，03，04号同学。

运行结果

结果是正确的，我们来分析一下我们的思路 1.首先我们要查询01同学选择的课程

select SId,CId from SC where SId='01'

2.然后找到每个同学所选择的课程这里我们以05同学来分析

select SId,CId from SC b where b.SId='05'

3.把两个查询的结果分别看成两张表，比如取名为a,b，如果表a中有表b不存在的数据，证明b同学选的课程肯定和a同学的课程不一样。否则b同学选了a同学的所有课程。 我们怎么用sql筛选出a表中b表不存在的数据呢，上代码：

select SId,CId from SC a where a.SId='01' and not exists( select b.SId from SC b where b.SId='05' and a.CId=b.CId )

这个sql外层是表示查询学生号为01选择的课程id,子查询是查询学生号为05的课程id，然后外层结果逐个比较子查询结果的课程号是否相等，如果有相等的，那么继续下一条，如果所有的都不等，那么返回外层结果的这条记录，也就用not exists连接，我们来分析运行过程。 1.首先查询出01号同学选择的课程，一共有3条

SIdCId010101020103

2.第一条数据进入子查询

01 | 01

3.子查询结果为

SIdCId05010502

4.逐个对比，条件为a.CId=b.CId，比较课程号是否相同

5.发现有01=01，则有记录返回，则not exists返回false,排除01|01这种情况，继续选择01|02，发现还是一样，最后01|03这种情况，发现b表没有这条数据，not exists返回true，则返回01|03这条记录。

6.我们得到了一个数据，如果在这条数据前面加个not exists,那么将返回false，也就是在最后面加的查询中05号同学与01号同学选择的课程不一样，05号同学的信息则不会进入最终的结果。也就是上述代码的由来。

修改bug

我们上述代码有个小bug，就是如果b表中的数据在a表中没有，也就是b包含a，那么b还是会加入到最终结果。 例如： a表数据为

SIdCId010101020103

b表数据为

SIdCId0401040204030404

那么我们运行的sql结果还是01，02，03，04四个同学的信息，原来我们只判断了a表中b表不存在的数据，没有判断b表中a表不存在的数据，只判断一个方向不能判断完全一样，只能判断a同学选择了b同学的所有选择的课程，我们双向筛选就没问题了。 代码：

select SId,Sname,Sage,Ssex from Student where not exists( select SId,CId from SC a where a.SId='01' and not exists( select b.SId from SC b where Student.SId=b.SId and a.CId=b.CId ) union select SId,CId from SC a where Student.SId=a.SId and not exists( select b.SId from SC b where b.SId='01' and a.CId=b.CId ) )

这样的sql查询效率还是蛮高的，做sql优化的时候，尽量不用or,in,not in,这样有可能在有索引的时候都不会走索引会全表查询。我们用union代替or，用exists代替in,not exists代替not in，欢迎大家讨论，有错误也欢迎大家指正。