在经理下选择Employyes [英] Selecting Employyes under a manager

问题描述

我有一个名为employee的表包含值

   eid名称管理员类型  
  1  Mike  3   1  
  2  David  3   2  
  3  Roger NULL 
  4 结婚 2   1  
  5  Joseph  2   2  

修正了经理只有2名员工

i想要写一个产生如下结果的查询

   经理Employe1 Employee2  
 roger Mike David 
 David Joseph Marry 

提前感谢

解决方

尝试

  SELECT  M.Name  AS 经理，Employe1，Employe2 
  FROM  
（
  SELECT 名称，eID  FROM 员工
  WHERE  eID  IN （ SELECT 经理 FROM 员工）
）M 
  INNER   JOIN  
（
  SELECT 名称 AS  Employe1，Manager，类型 
  FROM 员工
  WHERE  类型 =  1  
）E1  ON  M.eID = E1.Manager 
  INNER   JOIN  
（
  SELECT 名称 AS  Employe2，Manager，类型 
  FROM 员工
  WHERE  类型 =  2  
）E2  ON  M.eID = E2.Manager 

ihave a table named employee contain value

eid name  Manager type
1   Mike    3     1
2   David   3     2
3   Roger   NULL
4   Marry   2     1
5   Joseph  2     2

It is fixed that a manager has only 2 Employee
i want to write a query which produce result like below

Manager   Employe1  Employee2
roger     Mike       David
David     Joseph     Marry     

thanks in advance

解决方案

Try

SELECT M.Name AS Manager, Employe1, Employe2
FROM
(
SELECT Name, eID FROM Employee
WHERE eID IN (SELECT Manager FROM Employee)
) M
INNER JOIN
(
    SELECT Name AS Employe1, Manager,Type
    FROM Employee
    WHERE Type = 1
) E1 ON M.eID = E1.Manager
INNER JOIN
(
    SELECT Name AS Employe2 , Manager,Type
    FROM Employee
    WHERE Type = 2
) E2 ON M.eID = E2.Manager

这篇关于在经理下选择Employyes的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！