请问这个查询语句该怎么写呀?
use pubs
GO
if exists( select * from sysobjects where name = 'selectCourse')
drop table selectCourse
if exists( select * from sysobjects where name = 'student' )
drop table student
if exists( select * from sysobjects where name = 'course' )
drop table course
GO
create table student --学生
(
SNO varchar(10) not null, --学号
SName varchar(10) --姓名
)
create table course --课程
(
CNO varchar(10) not null, --课程号
CName varchar(10), --课程名
CTeacher varchar(10) --教师
)
create table selectCourse --选课
(
SNO varchar(10),
CNO varchar(10),
SCGRADE float --平均分
)
GO
alter table student add constraint stuPK_SNO primary key(SNO)
alter table course add constraint couPK_CNO primary key(CNO)
alter table selectCourse add constraint scFK_SNO foreign key(SNO) references student(SNO)
alter table selectCourse add constraint scFK_CNO foreign key(CNO) references course(CNO)
GO
insert into student
select 's001', '张三' union
select 's002', '李四' union
select 's003', '王五' union
select 's004', '赵六' union
select 's005', '王刚'
insert into course
select '1', '语文', '邹建' union
select '2', '数学', '李明' union
select '3', '英语', '阿飞'
GO
insert into selectCourse
select 's001', '1', 75 union
select 's003', '1', 87 union
select 's004', '1', 91 union
select 's002', '2', 78 union
select 's003', '2', 82 union
select 's004', '2', 85 union
select 's001', '3', 93 union
select 's002', '3', 74 union
select 's005', '3', 88
GO
-------------------------------------------------------------------
数据我都插好了... 小弟接触SQL没多久, 这条实在不会写了
要找出 没有 选修过 "李明" 老师讲授课程的所有 学生姓名
请指点,谢谢!
问题点数：0、回复次数：15Top
1 楼dawugui（潇洒老乌龟）回复于 2006-10-02 21:19:39 得分 0
select distinct student.SName from student , course , selectCourse
where student.SNO = selectCourse.SNO and
selectCourse.CNO not in (select CNO from course where CTeacher = '李明')
Top
2 楼lyd8935（）回复于 2006-10-02 21:40:49 得分 0
楼上的大哥...你这个似乎不行呀...
Top
3 楼dawugui（潇洒老乌龟）回复于 2006-10-02 21:47:05 得分 0
select distinct student.SName from student , course , selectCourse
where student.SNO = selectCourse.SNO and
select selectCourse.CNO from selectcourse where cno not in (select CNO from course where CTeacher = '李明')
Top
4 楼lyd8935（）回复于 2006-10-02 22:24:34 得分 0
汗..楼上的大哥..你的答案运行不了呀, 提示有错误, 我的是 sqlserver2000
Top
5 楼dawugui（潇洒老乌龟）回复于 2006-10-02 22:27:39 得分 0
我试试先,不好意思.Top
6 楼lyd8935（）回复于 2006-10-02 22:36:24 得分 0
偶晕死....Top
7 楼dawugui（潇洒老乌龟）回复于 2006-10-02 22:44:44 得分 0
select sno , SName
from
student
where sno not in
(select sno from
(select sno , CNO from selectcourse where cno in (select CNO from course where CTeacher = '李明')) as b)
sno SName
---------- ----------
s001 张三
s005 王刚
（所影响的行数为 2 行）
Top
8 楼dawugui（潇洒老乌龟）回复于 2006-10-02 22:45:05 得分 0
这下不会死了.Top
9 楼lyd8935（）回复于 2006-10-02 22:45:52 得分 0
to: dawugui(潇洒老乌龟)兄
谢谢,辛苦了..Top
10 楼dawugui（潇洒老乌龟）回复于 2006-10-02 22:47:45 得分 0
这应该是你要的结果吧?Top
11 楼lyd8935（）回复于 2006-10-02 22:53:01 得分 0
恩, 是的..
过程真的好复杂呀...
头晕晕Top
12 楼dawugui（潇洒老乌龟）回复于 2006-10-02 22:56:58 得分 0
1\找出参加了李明老师的学生
select sno , CNO from selectcourse where cno in (select CNO from course where CTeacher = '李明'))
2\取出列SNO,SQL在使用IN时只能一列
(select sno from
(select sno , CNO from selectcourse where cno in (select CNO from course where CTeacher = '李明')) as b)
3\再找出没有在上面第二中包含的sno
select sno , SName
from
student
where sno not in
(select sno from
(select sno , CNO from selectcourse where cno in (select CNO from course where CTeacher = '李明')) as b)
Top
13 楼lyd8935（）回复于 2006-10-02 23:04:14 得分 0
2\取出列SNO,SQL在使用IN时只能一列
(select sno from
(select sno , CNO from selectcourse where cno in (select CNO from course where CTeacher = '李明')) as b)
这一句中的 as b, 是不是把 b 当做一个临时表 b(SNO, CNO),
从而完成 select SNO from b 的查询.
是这样的吗?Top
14 楼dawugui（潇洒老乌龟）回复于 2006-10-02 23:08:40 得分 0
可以这么说.
但不叫临时表,叫表的别名.Top
15 楼lyd8935（）回复于 2006-10-03 08:41:50 得分 0
哦,明白了，明白了, 谢谢!Top
