mysql - 找到销售人员的经理

Question

我的表是'DESIGNATION'

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

我想通过他们的 DEPT_NAME 获得经理姓名....

平均结果看起来像

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`

但它不起作用...

Answer 1

你只需要一个 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通过 E_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)