mysql - 如何修复错误代码 1241：操作数应在 mysql 查询中包含 1 列？

Question

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++ 的男学生，为此我需要加入学生、注册和课程表，

标签： mysqljoinselectleft-joinmysql-error-1241

Answer 1

由于您使用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