mysql：子查询返回超过1行

I was trying to solve this question given below by using the subquery in MySQL but can't find a solution as it's showing subquery returns more than one row.

我试图通过使用MySQL中的子查询来解决下面给出的这个问题,但找不到解决方案,因为它显示子查询返回多行。

Problem Statement: Display the emp name with their salaries and their managers and managers salary.
Display those only employees whose managers were hired after them.

问题陈述:显示emp名称及其薪水及其经理和经理薪水。显示那些经理被雇用的员工。

select w.ename employee, w.SAL woSal, m.ename manager, m.SAL mSal
from emp w, emp m
where w.mgr = m.empno and
(SELECT E1.ENAME  
FROM EMP E1, EMP E2
WHERE E1.MGR=E2.EMPNO and ( E1.hiredate<E2.hiredate));

1 个解决方案

#1

You don't need a subquery - just add the condition on the hiredate to the join condition. Also note that implicit joins (using more than one table in the from clause) is considered deprecated, and you'd probably be better off rewriting it as an explicit join:

您不需要子查询 - 只需将hiredate上的条件添加到连接条件即可。另请注意,隐式连接(在from子句中使用多个表)被认为已弃用,并且您最好将其重写为显式连接:

SELECT w.ename employee, w.SAL woSal, m.ename manager, m.SAL mSal
FROM   emp w
JOIN   emp m
WHERE  w.mgr = m.empno and w.hiredate < m.hiredate