mysql - 嵌套选择不接受多于一列

Question

我有一些这样的表:

departments

dept_no char(4) primary key

dept_name varchar(40) unique

dept_emp

emp_no int primary key

dept_no char(4) primary key

from_date date to-date date

dept_manager

dept_no char(4) primary key

emp_no int primary key

from_date date

to_date date

employees

emp_no int primary key

first_name varchar(14)

last_name varchar(16)

现在我有:

select employees.emp_no, employees.first_name,employees.last_name,
departments.dept_name, (select employees.first_name 
from employees where dept_manager.emp_no =employees.emp_no )
from employees,dept_emp ,departments,dept_manager
where (employees.emp_no=dept_emp.emp_no) AND 
(dept_emp.dept_no = departments.dept_no)
LIMIT 300;

第二个选择是打印每个员工的经理姓名。

但是当我尝试打印经理的姓氏时，它会抛出错误。

select employees.emp_no, employees.first_name,employees.last_name,
    departments.dept_name, (select employees.first_name, **employess.last_name**
    from employees where dept_manager.emp_no =employees.emp_no )
    from employees,dept_emp ,departments,dept_manager
    where (employees.emp_no=dept_emp.emp_no) AND 
    (dept_emp.dept_no = departments.dept_no)
    LIMIT 300;

错误 1241 (21000):操作数应包含 1 列

为什么会出现这个错误？

Answer 1

这不是子查询的工作方式，您应该为每个查询选择一列用作列

select employees.emp_no, employees.first_name,employees.last_name,
departments.dept_name, (select employees.first_name
from employees where dept_manager.emp_no =employees.emp_no ) e_first_name,
(select employess.last_name
from employees where dept_manager.emp_no =employees.emp_no ) e_last_name
from employees,dept_emp ,departments,dept_manager
where (employees.emp_no=dept_emp.emp_no) AND 
(dept_emp.dept_no = departments.dept_no)
LIMIT 300;

或者您可以使用 CONCAT() 合并两列中的名称，例如

select CONCAT(employees.first_name,'  ',employess.last_name) full_name