select firstName, lastName from students, courses, registration
where students.studentID = registration.studentID
and courses.courseCode = registration.courseCode
and gender = 'M' and courseName = 'Database Systems'
in(select firstName, lastName
from students, courses, registration
where students.studentID = registration.studentID
and courses.courseCode = registration.courseCode
and gender = 'M' and courseName = 'C++');``
我需要找到同时学习了数据库系统和C++的男学生,要做到这一点,我需要离开加入表学生,注册和课程,
您的in子句缺少某些列尝试
select
firstName, lastName
from students, courses, registration
where students.studentID = registration.studentID
and courses.courseCode = registration.courseCode
and gender = 'M' and courseName = 'Database Systems'
and students.studentID
in (select studentID
from students, courses, registration
where students.studentID = registration.studentID
and courses.courseCode = registration.courseCode
and gender = 'M' and courseName = 'C++');
由于使用in
运算符的方式,查询失败,该运算符需要列名或左侧的表达式。
根据你对目标的描述,我怀疑你的查询可以重写为使用exists
条件和相关的子查询进行过滤,比如:
select
firstName,
lastName
from students s
where
gender = 'M'
and exists(
select 1
from courses c
inner join registration r on c.courseCode = r.courseCode
where
c.courseName = 'Database Systems'
and s.studentID = r.studentID
)
and exists(
select 1
from courses c
inner join registration r on c.courseCode = r.courseCode
where
c.courseName = 'C++'
and s.studentID = r.studentID
)
另一种可能的解决方案是使用聚合,并使用having
子句进行过滤:
select s.firstName, s.lastName
from students s
inner join registration r
on s.studentID = r.studentID
inner join courses c
on c.courseCode = r.courseCode
and c.courseName in ('Database Systems', 'C++' )
where s.gender = 'M'
group by s.studentID, s.firstName, s.lastName
having count(distinct c.courseName) = 2
使用HAVING
子句可以更容易地编写此查询,以检查学生从集合("数据库系统"、"C++"(中学习的课程数是否为2:
SELECT s.studentID, s.firstName, s.lastName
FROM students s
JOIN registration r ON s.studentID = r.studentID
JOIN courses c ON c.courseCode = r.courseCode
WHERE s.gender = 'M' AND c.courseName IN ('Database Systems', 'C++')
GROUP BY s.studentID, s.firstName, s.lastName
HAVING COUNT(DISTINCT c.courseName) = 2
注意,我已经用ON
条件以首选样式重写了您的JOIN
。