SQL除法加强版

接之前的SQL除法，我们来一次SQL除法的加强版。实际工作中这种需求可能很少，但是为应付面试题，没准有用。

1.数据脚本

drop table student;

drop table subject;

create table student(stu_name varchar2(20),subject_id varchar2(10));

create table subject(subject_id varchar2(10),subject_name varchar2(20),teacher varchar2(20));

insert into student values('狗蛋','M');

insert into student values('狗蛋','C');

insert into student values('狗蛋','P');

insert into student values('翠花','M');

insert into student values('翠花','C');

insert into student values('小红','M');

insert into student values('小红','P');

insert into student values('小美','M');

insert into student values('小美','T');

insert into student values('阿健','M');

insert into student values('阿健','C');

insert into student values('阿健','P');

insert into student values('阿健','T');

insert into subject values('M','数学','张老师');

insert into subject values('C','语文','张老师');

insert into subject values('M','数学','李老师');

insert into subject values('C','语文','李老师');

insert into subject values('P','物理','李老师');

insert into subject values('M','数学','孙老师');

insert into subject values('P','物理','孙老师');

insert into subject values('T','体育','何老师');

commit;

 

2. SQL需求

要求检索学生的选课来自同一位老师的学生信息和老师信息。

3. SQL实现

select distinct sd1.stu_name,sj1.teacher

from student sd1,

     subject sj1

where not exists (select 1  --①

                  from student sd2 --②

                  where sd1.stu_name=sd2.stu_name

                    and not exists (select 1  --④

                                    from subject sj2

                                    where sd2.subject_id=sj2.subject_id

                                      and sj1.teacher=sj2.teacher)) --③

order by 1,2;

--①，查询返回的条件是这一层not exists返回0条数据

--②，sd1和sd2用stu_name进行关联，以学生为单位，从sd2中得到学生的选课（subject_id）结果集

--③，用sj1和sj2的teacher进行关联，得到老师教课的结果集

--④，只有②的每一条subject_id都在这一层not exists检索的subject_id中，查询①处的结果才是0条，即要求②是③的子集