表分别是学生表(student)字段id,sname;课程表(course)字段id,cname;选课表choose_course,字段sid对应student的id,cid对应course的idselect distinct sname from student s
where not exists(
select * from course c
where not exists(
select * from choose_course cc
where cc.cid=c.id and cc.sid=s.id))
请大家帮我解释下这个题目。这段select 就是弄不明白,
for id in(select * from student s)
loop
if(not exists(
for id in(select * from course c)
loop
if(no t exists(select * from choose_course cc where cc.cid=c.id and cc.sid=s.id))
then
output the record
end if
end loop
))
then
output the record
end if
end loop我给它这样解释的,还是不懂
where not exists(
select * from course c
where not exists(
select * from choose_course cc
where cc.cid=c.id and cc.sid=s.id))
请大家帮我解释下这个题目。这段select 就是弄不明白,
for id in(select * from student s)
loop
if(not exists(
for id in(select * from course c)
loop
if(no t exists(select * from choose_course cc where cc.cid=c.id and cc.sid=s.id))
then
output the record
end if
end loop
))
then
output the record
end if
end loop我给它这样解释的,还是不懂
select sname from student s
where not exists(
select * from course c
where not exists(
select * from choose_course cc
where cc.cid=c.id and cc.sid=s.id))
一个项目涉及到的50个Sql语句(整理版)
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html
-- 3. 查出没选择课程的集合为空的学生
select distinct name
from student s
where not exists
(
-- 2.查出每个学生没有选择的课程
select * from course c
where not exists
(
-- 1.查出每个学生选择的课程
select * from choose_course cc
where cc.cid=c.id and cc.sid=s.id
)
);
总体了是查询选择了课程的学生的姓名。select distinct name -- 2查询选择了课程的学生姓名
from student s
where not exists
(
select * from course c -- 1查询没有被选择的课程
where not exists
(
select * from choose_course cc
where cc.cid=c.id and cc.sid=s.id
)
);
loop
for rows in(select * from course c)
loop
if(not exists(select * from choose_course cc where cc.cid=c.id and cc.sid=s.id))
then
--output the record 这里没有输出记录,只是返回了一个集合给外层SELECT
return course record -- 1. 如果当前学生没有选择当前课程,则在课程子查询集合中加入该课程的记录
end if
end loop
if(not exists(course record))
then
output the record -- 如果当前学生的未选择课程的集合为空,则输出该学生的记录
end if
end loop