编写一个sql查询，选择听过'John'教授所有讲座但没有听过'Joseph‘教授任何讲座的学生的名字。
我写了这个查询，但是它给了我一个学生的名字，他们也在接受约瑟夫的讲座。
select distinct s.studentname
from Student s,Attendance a,Lecture l
where s.StudentId=a.studid
and l.lecId = a.lectureid
and l.professor='JOHN'
and l.professor != 'JOSEPH';
我在上面的查询中犯了一些错误，但无法识别相同的错误。
下面是表的结构
学生桌
# StudentId, StudentName, Sex
'1', 'AMY', 'M'
'2', 'JACK', 'M'
'3', 'TONY', 'M'
'4', 'TARA', 'M'
'5', 'SARAH', 'F'
'6', 'TOM', 'F'
讲台
# LecId, Subject, Professor
1, MATH, JOHN
2, MATH, JOSEPH
3, PHY, MARK
4, PHY, MAX
5, PHY, JOHN
6, CHEM, JOHN
7, CHEM, JOSEPH
8, HISTORY, JOSEPH
考勤表
# StudId, LectureId
'1', '1'
'1', '2'
'3', '1'
'2', '5'
'2', '6'
'3', '4'
'1', '6'
'4', '5'
'5', '1'
'5', '2'
'1', '3'
'1', '4'
'1', '5'
'1', '6'
您需要查看属于给定学生的所有行，因此一个简单的where子句无法完成您想要的任务。 相反，您可以使用带有having子句的聚合和filer:
select s.studentname
from student s
inner join attendance a on s.studentId = a.studid
inner join lecture l on l.lecId = a.lectureid
group by s.studentId, s.studentname
having max(l.professor= 'JOHN') = 1 and max(l.professor = 'JOSEPH') = 0
如果你希望教师能补充约翰的所有讲课，而不是补充约瑟的讲课，那么:
select s.studentname
from student s
inner join attendance a on s.studentId = a.studid
inner join lecture l on l.lecId = a.lectureid
group by s.studentId, s.studentname
having
sum(l.professor= 'JOHN') = (select count(*) from lecture where professor = 'JOHN')
and max(l.professor = 'JOSEPH') = 0
如果您的MySQL版本支持GROUP_CONCAT，您可以尝试下面的脚本-
此处演示
SELECT S.StudentId,S.StudentName, GROUP_CONCAT(DISTINCT L.Professor)
FROM Attendance A
INNER JOIN Lecture L ON A.LectureId = L.LecId
INNER JOIN Student S ON A.StudId = S.StudentId
GROUP BY S.StudentId,S.StudentName
HAVING GROUP_CONCAT(DISTINCT L.Professor) = 'JOHN'
以上查询将返回仅参加“约翰”课程的学生名单。 没有其他人。
但是，如果您的要求改变为在列表中添加更多教授，则可以在HAVING子句检查中相应地添加教授名称。 但您需要在这种情况下应用适当的排序。
尝试:
select * from Student st
where exists(select * from Lecture l
join Attendance a
on l.LecId = a.LectureId
where l.Professor = 'John'
and a.StudId = st.StudentId)
and exists(select * from Lecture l
join Attendance a
on l.LecId = a.LectureId
where l.Professor = 'Joseph'
and a.StudId = st.StudentId)
