sql 语句 嵌套子查询 执行顺序分析

--创建测试数据
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')

create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')
insert into Course values('04' , N'法语' , '03')

create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'张三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')

create table SC(S# varchar(10),C# 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)
insert into SC values('07' , '04' , 90)

 

题目：--7.查询学过“王五”老师所教的所有课的同学的学号、姓名；

答案：select s#,sname from Student where s# in(
select s# from SC,Course,Teacher where SC.C#=Course.C#
and Teacher.T#=Course.T# and Teacher.Tname='王五'
group by S# having COUNT(SC.C#)=(
select COUNT(c#) from Course,Teacher where Teacher.T#=Course.T# and Tname='王五'))

自己的答案：

select a.s#,a.Sname,a.score,b.score2 from (select Student.Sname,Student.S#,SC.score from sc,student where SC.S#=Student.S# and sc.c#='02') a ,
(select Student.Sname,Student.S#,SC.score as score2 from SC,Student where sc.S#=Student.S# and sc.C#='01') b where a.s#=b.s# and score2 <score

对其中的 的部分语句进行分析（是可单独执行到的）

SELECT Student.s#,Student.Sname,score ,
(SELECT score FROM SC SC_2 WHERE SC_2.s#=Student.s# AND SC_2.c#='01') score2
FROM Student,SC WHERE Student.s#=SC.s# AND c#='02'

通过看执行计划，可知，是先执行的子查询，然后在执行外面的。

如果细心的去看，你会发现这个语句的查询与子查询的表是一样的，所以我们可以试想一下，如果我把这个语句分开分别得到什么结果：

select Student.Sname,Student.S#,SC.score from sc,student where SC.S#=Student.S# and sc.c#='02'

select Student.Sname,Student.S#,SC.score as score2 from SC,Student where sc.S#=Student.S# and  sc.C#='01'

那么如果我们在通过where 或者left,right连接会有什么不同呢，你会发现，上面的子查询相当是分别满足某些条件的数据（两个基本相同的表的数据）进行left关联