mysql - 如果值存在于同一个表的另一列中，如何找到相应的列值

Question

employee_name 列包含所有员工姓名及其对应的employee_ID，manager_ID 列包含所有员工的经理的employee_ID

在这里我想让所有员工都知道他们的mangers_names

注意:经理也是公司的员工，因此 manager_ID ,employee_ID包含相同的值

请参见下表

select * from  test1.employee;

Employee_id Employee_Name  Manager_ID
1124        Annapurna      1125 
1125        Jaseel         
1126        Shilpa         1125

select employee_Name, employee_Name as manager_Name from
employee   where Employee_id  in (select Manager_ID from employee ) ;

RESULT:
employee_Name manager_Name
Jaseel         Jaseel         

select * from  test1.employee;

select employee_Name, employee_Name as manager_Name from
 employee   where Employee_id  in (select Manager_ID from employee ) ;


employee_Name manager_Name
Annapurna     jaseel
Shilpa        Annapurna

Answer 1

假设您只想搜索上一级(如示例数据所示)，您可以直接自连接表，例如:

SELECT 
    t1.Employee_Name,
    t2.Employee_Name Manager_Name
FROM
    test1.employee t1
    LEFT JOIN test1.employee t2 
        ON t2.Employee_id = t1.Manager_id

注意:由于我们使用 LEFT JOIN，如果员工没有经理，它将出现在结果集中，且 Manager_name 为 NULL。