sql - 无法在多个表之间进行所需的联接

Question

我有3张 table

计划:

 plan_id   emp_id   duration
  123       1010    30
  456       1011    40
  789       1012    60

计划:

plan_id emp_id
 123    2131
 456    3131
 789    4131

雇员:

emp_id  Name
1010    Andy
1011    Cole
1012    John
2131    Sam
3131    Kim
4131    Ray

所需的摘要输出:

plan_id   Name   duration
 123      Andy   30
 123      Sam    30
 456      Cole   40
 456      Kim    40
 789      John   60
 789      Ray    60

我试图修改以获得上述结果的查询:

SELECT P.plan_id 
      ,E.Name
      ,P.duration
FROM Plan P
LEFT JOIN Emp E
    ON P.emp_id = E.emp_id
LEFT JOIN PlanEmp PE
    ON P.plan_id = PE.plan_id

我无法弄清楚如何使用 PlanEmp 表和 Plan 表提取员工详细信息以获取摘要输出。

Answer 1

这应该有效:

SELECT sub.plan_id, sub.emp_id, Emp.name, Plan.duration
FROM
(SELECT plan_id, emp_id
FROM Plan
UNION
SELECT plan_id, emp_id
FROM PlanEmp) sub
LEFT JOIN Emp
ON sub.emp_id = Emp.emp_id
LEFT JOIN Plan
ON sub.plan_id = Plan.plan_id
ORDER BY plan_id

在这里测试:http://sqlfiddle.com/#!9/21ca79/4