请教一个有意思的SELECT查询问题

[vanneo]

现有两个表：
1：students 表
NAME SNO
SMITH 17
BROWN 8
NELSON 15

2：GRADES 表
SNO CNO GRADE
17 112 B
17 119 C
8 85 A
8 92 A
8 102 B
8 135 A
15 135 A
15 112 A

现如何用SELECT查询：修过的课程，且成绩全部都为A的学生姓名。

[OracleRoob]

create table #students(NAME varchar(100),SNO int)
create table #GRADES(SNO int,CNO int,GRADE varchar(100))

insert into #students
select 'SMITH',17 union all
select 'BROWN', 8 union all
select 'NELSON',15

insert into #GRADES
select 17,112,'B' union all
select 17,119,'C' union all
select 8,85,'A' union all
select 8,92,'A' union all
select 8,102,'B' union all
select 8,135,'A' union all
select 15,135,'A' union all
select 15,112,'A'

select * from #students where SNO not in (select sno from #GRADES where GRADE<>'A')

drop table #students,#GRADES

/*
SNO SNO
--------------------------
NELSON 15

*/

[aniude]

思路不错，学习

[vanneo]

谢谢！步佩服不行，接分。

[vanneo]

wangtiecheng(不知不为过，不学就是错！) 兄的解法可能还是有些问题，下面的方法不知是否可行？

select NAME from students where SNO in (select sno from GRADES) and SNO not in (select sno from GRADES where GRADE<>'A')

或者

select NAME from students where SNO in (select sno from GRADES where SNO not in (select sno from GRADES where GRADE<>'A'))