mysql - 寻找销售员工的经理

我的桌子是“指定的”

ID    ||    DEPT_ID     ||      E_NAME       ||      DESIGNATION
1     ||     12         ||        A          ||       EMPLOYEE
2     ||     12         ||        B          ||       MANAGER
3     ||     12         ||        C          ||       EMPLOYEE
4     ||     14         ||        D          ||       MANGER
5     ||     14         ||        E          ||       EMPLOYEE
6     ||     14         ||        F          ||       EMPLOYEE

我要经理的名字通过他们部门的名字。。。。
平均结果看起来像

ID    ||    DEPT_ID     ||      E_NAME       ||      DESIGNATION  ||   MANAGER
1     ||     12         ||        A          ||       EMPLOYEE    ||    B
2     ||     12         ||        B          ||       MANAGER     ||    B
3     ||     12         ||        C          ||       EMPLOYEE    ||    B
4     ||     14         ||        D          ||       MANGER      ||    D
5     ||     14         ||        E          ||       EMPLOYEE    ||    D
6     ||     14         ||        F          ||       EMPLOYEE    ||    D

我的问题是

SELECT `ID`,`DEPT_ID`,`ENAME`,`DESIGNATION`,
    (select `ENAME` from `DESIGNATION` where
        (select `E_NAME` from `DESIGNATION` where
            (SELECT `DEPT_ID` FROM `DESIGNATION` WHERE `DESIGNATION` = 'EMPLOYEE')
            =
            (SELECT `DEPT_ID` FROM `DESIGNATION` WHERE `DESIGNATION` = 'MANAGER') and `DESIGNATION`='MANAGER')
    AS MANAGER
from `DESIGNATION`

但它不起作用。。。

最佳答案

你只需要一个JOIN operation。这是使用数据库时的基本概念。你应该花点时间阅读一下。
像那样？

SELECT A.*, B.E_NAME
   FROM DESIGNATION AS A, DESIGNATION AS B
   WHERE B.DESIGNATION = "MANAGER"
     AND A.DEPT_ID = B.DEPT_ID

或者使用显式JOIN语法：

SELECT A.*, B.E_NAME
   FROM DESIGNATION AS A JOIN DESIGNATION AS B USING (DEPT_ID)
   WHERE B.DESIGNATION = "MANAGER"

编辑：
如果可以有多个管理器，则可以将GROUP_CONCAT聚合函数与显式group byE_NAME一起使用（假设这是唯一键）：

SELECT A.*, GROUP_CONCAT(B.E_NAME)
   FROM DESIGNATION AS A, DESIGNATION AS B
   WHERE B.DESIGNATION = "MANAGER"
     AND A.DEPT_ID = B.DEPT_ID
   GROUP BY(A.E_NAME)