我有两个表Employees
和Assignments
与内容如下面(分别输出A和B)的图像中可见。每个员工Employees
都有最大。1 个项目负责人 ( Assignment.A_Leader = 1
) 和最多 1 个项目控制员 ( Assignment.A_Controller = 1
) 分配。我想得到一个所有员工的名单,他们是领导或控制者的项目。如果没有为员工分配此类任务,NULL
则应出现。我设法列出领导或项目分配。但是,我无法按照此处的建议将两者放在正确的列表中(参见尝试 E)。为了获得所需的决赛桌,我必须添加什么?
以下代码的字母 AE 对应于图像中输出的字母 AE。
我正在使用 Microsoft SQL Server 2017。
-- A: all employees
SELECT * FROM Employee
-- B: all assignments
SELECT * FROM Assignment
-- C: employees with their projects as LEADERS
SELECT E.E_FirstName as First, E.E_LastName as Last, L.A_ProjectID as Leader
FROM Employee E LEFT JOIN
(SELECT A.E_ID, A_ProjectID FROM Assignment A WHERE A.A_Leader=1) L
ON E.E_ID = L.E_ID
-- D: employees with their projects as CONTROLLERS
SELECT E.E_FirstName as First, E.E_LastName as Last, C.A_ProjectID as Controller
FROM Employee E LEFT JOIN
(SELECT A.E_ID, A_ProjectID FROM Assignment A WHERE A.A_Controller=1) C
ON E.E_ID = C.E_ID
-- E: employees with their projects as LEADERS or CONTROLLERS
SELECT X.First, X.Last, X.Leader, Y.Controller FROM
(SELECT E.E_FirstName as First, E.E_LastName as Last, L.A_ProjectID as Leader
FROM Employee E LEFT JOIN
(SELECT A.E_ID, A_ProjectID FROM Assignment A WHERE A.A_Leader=1) L
ON E.E_ID = L.E_ID) as X,
(SELECT E.E_FirstName as First, E.E_LastName as Last, C.A_ProjectID as Controller
FROM Employee E LEFT JOIN
(SELECT A.E_ID, A_ProjectID FROM Assignment A WHERE A.A_Controller=1) C
ON E.E_ID = C.E_ID) as Y
你可以left join
在assignments
表两次:
select
e.e_first_name,
e.e_last_name,
al.a_project_id leader,
ac.a_project_id controller
from employee e
left join assignments al on al.e_id = e.e_id and al.a_leader = 1
left join assignments ac on ac.e_id = e.e_id and ac.a_controller = 1
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句