Question

SQL求大神看看这道题怎么做？

表：student(sno,sname,ssex,sbirthday,class)
score(sno,cno,degree)
course(cno,cname,tno)
teacher(tno,tname,tsex,tbirthday,prof,depart)
题：查询和“小王”不同班并同个教师的同学Sname。

Answer 1

select sname from student
where sno in (
select sno from student,score,course,teacher
where student.sno=score.sno
and course.cno=score.cno
and teacher.tno=course.tno
and student.sname='小王'
and student.class not in(select class from student where sname='小王')
)

追问

服务器: 消息 209，级别 16，状态 1，行 1
列名 'SNO' 不明确。。。

追答

select sname from student
where sno in (
select student.sno from student,score,course,teacher //这里的忘记加了
where student.sno=score.sno
and course.cno=score.cno
and teacher.tno=course.tno
and teacher.tno in (
select teacher.tno from student,score,course,teacher
where student.sno=score.sno
and course.cno=score.cno
and teacher.tno=course.tno
and student.sname='小王'
)
and student.class not in(select class from student where sname='小王')
)