mysql - 多个表选择，子查询返回超过 1 个值。数据库

Question

我有两个表:

员工

id  | fullName   | birth      | speciality
1   | A A A      | 01/01/1980 | Manager
2   | B B B      | 01/01/1980 | Developer
3   | C C C      | 01/01/1980 | User

员工状况

ID  | status     | dateChange
1           | 1          | 01/01/2010
2           | 1          | 01/01/2013
3           | 1          | 01/01/2015
3           | 2          | 01/01/2016

我想选择以下数据

ID | Full name | Bith date | speciality | Date hired | Date fired

结果必须是:

ID | Full name    | Bith date   | speciality | Date hired | Date fired
1  | A A A        | 01/01/1980  | Manager    | 01/01/2010 | null
2  | B B B        | 01/01/1980  | Developer  | 01/01/2013 | null
3  | C C C        | 01/01/1980  | User       | 01/01/2015 | 01/01/2016
3  | C C C        |01/01/1980   | User       | 01/01/2017 | null

我的代码:

 SELECT Employees.id , Employees.fullName, Employees.birth, Employees.speciality,
(SELECT dateChange FROM EmployeesStatus WHERE status=1 AND id=Employees.id) datehired,
(SELECT dateChange FROM EmployeesStatus WHERE status=2 AND id=Employees.id) datefired FROM Employees

结果是以下消息:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,

, >= or when the subquery is used as an expression.

有什么想法吗？

Answer 1

你应该使用连接而不是基于子查询的=

SELECT 
    Employees.id 
  , Employees.fullName
  , Employees.birth
  , Employees.speciality
  , e1.dateChange as datehired, e2.dateChange as datefired
FROM Employees
INNER JOIN EmployeesStatus es1 on e1.status=1 AND e1.id=Employees.id
LEFT JOIN EmployeesStatus es2 on e2.status=2 AND e2.id=Employees.id

或者你可以在子查询中使用 in 子句而不是 =