Select StuId,StuName From tblStudent st Where not exists
(
Select CourseID From tblCourse cu Inner Join tblTeacher tc On cu.TeaID=tc.TeaID
Where tc.TeaName='叶平' And CourseID not in
(Select CourseID From tblScore Where StuID=st.StuID)
)
表结构如下:
tblStudent (stuid,stuname,stusex,stuage);
tblCourse (stuid,courseid,score);
tblTeacher (teaid,teaname,courseid);
------解决方案--------------------
- SQL code
Select StuId,StuName From tblStudent st Where not exists --找学生信息部在(2) 里面的学生( Select CourseID From tblCourse cu Inner Join tblTeacher tc On cu.TeaID=tc.TeaID --去课程表 找到‘叶平’老师 并且 课程id 不在 (1) 里面的CourseID Where tc.TeaName='叶平' And CourseID not in (Select CourseID From tblScore Where StuID=st.StuID) --成绩表StuID和Student表StuID对应Course)--查看方法:从内往外----1:--成绩表StuID和Student表StuID对应CourseID--2:--去课程表 找到‘叶平’老师 并且 课程id 不在 (1) 里面的CourseID--3:--找学生信息部在(2) 里面的学生