1.已知SQL关系模式 s(s#,sname)
c(c#,cname)
sc(s#,c#,score)create table s( s# varchar (10), sname varchar (10));
go
create table c( c# varchar (10), cname varchar (10));
go
create table sc( s# varchar (10), c# varchar(10 ),score varchar(10 ));
goinsert into s values ('s1' ,'sa');
insert into s values ('s2' ,'sb');
insert into s values ('s3' ,'sc');
insert into s values ('s4' ,'sd');insert into c values ('c1' ,'ca');
insert into c values ('c2' ,'cb');
insert into c values ('c3' ,'cc');
insert into c values ('c4' ,'cd');insert into sc values ('s1' ,'c2', '50');
insert into sc values ('s1' ,'c4', '50');
insert into sc values ('s2' ,'c1', '60');
insert into sc values ('s2' ,'c2', '70');
insert into sc values ('s3' ,'c1', '80');
insert into sc values ('s3' ,'c3', '90');
insert into sc values ('s4' ,'c1', '80');
insert into sc values ('s4' ,'c2', '90');
insert into sc values ('s4' ,'c3', '90');
insert into sc values ('s4' ,'c4', '90');
问题: 找出至少包含了S2选修了课程的学员答案:select *
from sc a
where not exists ( select *
from SC
where not exists ( select *
from SC C
where sc . c# = C. c#
and a . s# = c. s# )
and SC . s# = 'S2'
)答案是有了,不过很难理解各种逻辑,求大神解释。
c(c#,cname)
sc(s#,c#,score)create table s( s# varchar (10), sname varchar (10));
go
create table c( c# varchar (10), cname varchar (10));
go
create table sc( s# varchar (10), c# varchar(10 ),score varchar(10 ));
goinsert into s values ('s1' ,'sa');
insert into s values ('s2' ,'sb');
insert into s values ('s3' ,'sc');
insert into s values ('s4' ,'sd');insert into c values ('c1' ,'ca');
insert into c values ('c2' ,'cb');
insert into c values ('c3' ,'cc');
insert into c values ('c4' ,'cd');insert into sc values ('s1' ,'c2', '50');
insert into sc values ('s1' ,'c4', '50');
insert into sc values ('s2' ,'c1', '60');
insert into sc values ('s2' ,'c2', '70');
insert into sc values ('s3' ,'c1', '80');
insert into sc values ('s3' ,'c3', '90');
insert into sc values ('s4' ,'c1', '80');
insert into sc values ('s4' ,'c2', '90');
insert into sc values ('s4' ,'c3', '90');
insert into sc values ('s4' ,'c4', '90');
问题: 找出至少包含了S2选修了课程的学员答案:select *
from sc a
where not exists ( select *
from SC
where not exists ( select *
from SC C
where sc . c# = C. c#
and a . s# = c. s# )
and SC . s# = 'S2'
)答案是有了,不过很难理解各种逻辑,求大神解释。
我们先把第一个括号里面的东西拿出来
select *
from SC
where not exists ( select *
from SC C
where sc . c# = C. c#
and a . s# = c. s# )
and SC . s# = 'S2'
这里面别名a,也就是最外面的表,只带入了s#,也就是sc的所有学生,我们先取第一条记录的学生's1',带入理解,即成了
select *
from SC
where not exists ( select *
from SC C
where sc . c# = C. c#
and 's1' = c. s# )
and SC . s# = 'S2'
而这句,很明显的意思是,查询所有在学生s2的课程中,没有学生s1也在学习的课程
如果这个查询有值,那么,这个查询的not exists不成立,那最外面的别名为a的第一记录的学生's1'不成立
因此整体语句的意思即是
在别名a的表中的所有学生里,不存在( 学生S2有学的课程,而他(别名a的学生)没有学习的课程)不知这样有没有说清楚~~
2、SC
3、SC C可以将
第一个SC看做是取 S#,即学生
第二个SC看做是取 C#,即课程,而且是S2选修的课程
第三个SC里,可以看到两个条件,课程对应第二个SC的课程,就是S2选修的课程,在数据里反应是 C1 C2,到这里看第二个条件就很容易理解了,如果对于C1 C2两个课程,不存在该学生选修的话就排除掉,只有两个课程在该学生的选修课里都存在的时候查询出来。
感觉逻辑本身就点绕,不知道有没有更好的解决方案。SELECT * FROM SC
WHERE S# in(select S# from
(SELECT S#,COUNT(S#) as counts FROM (select S# from sc as a
where c# IN
(SELECT C# FROM Sc WHERE S#='S2')) as p
group by S#) as t
where t.counts=(select count(C#) from Sc where S#='S2'))
我看了性能还差一点,大概是我的57%,你的43%,这个好理解一点。
先找出所有学习了S2学习的课程。然后算出各个学生的学习了S2学习的课程的数量。
如果这个数量等于S2学习的课程数量(这里就是2)那么他肯定满足条件。如果小于肯定不满足。
针对这个有各种解释。这里面可以不用SC表3次。还有S,C表就OK 了。
你看看这个链接
http://wenku.baidu.com/link?url=BK8Yu_QrvPVhcRFUmUYyFr1qiqRCZ2tFgHstQriCY0VXm4DXRR1uSuOjOO_LsPkfdJU6OwlugB3Rud7wdWX0aOqtMf0k7F5R6IXOTLPG31u
里面有各种解释。
像这种范围的东西,感觉没有比EXISTS更好描述的SQL了~~
WITH CTE AS(
SELECT C# FROM SC WHERE sc . s# = 'S2'
)
,CTE2 AS(
SELECT S#,C# FROM s,cte
)
,CTE3 AS(
SELECT A.S# FROM CTE2 A LEFT JOIN SC B ON A.C#=B.C# AND A.S#=B.S# WHERE B.S# IS NULL GROUP BY A.S#
)
SELECT A.* FROM SC A LEFT JOIN CTE3 B ON A.S#=B.S# WHERE B.S# IS NULL
where not exists ( select *
from SC C
where sc . c# = C. c#
and 'S1'= c. s# )
这部分代码 的意思:选出 所有选修了 S1 学员未选修 的课程
例如:
S2 C1
S3 C1
S3 C3
S4 C1
S4 C3
=================================================================
select * from SC
where not exists ( select *
from SC C
where sc . c# = C. c#
and 'S1'= c. s# )
and SC.S# =‘S2'
这部分代码意思是: 选出 S2学员 选修了 S1 学员未选修 的课程
例如:S2 C1
========================================================================
select *
from sc a
where not exists ( select *
from SC
where not exists ( select *
from SC C
where sc . c# = C. c#
and a . s# = c. s# )
and SC . s# = 'S2'
)
第三部分代码的是意思其实就一条一条记录去判断是否符合要求:如果存在 S2学员选修了课程而该学员没选修,就剔除。
不知道 这样解释,大神们看看是否可以?