SQL:如何从具有OneTomany关系的表中创建数据库视图

Question

有三个可用表和列：

Core - EmpID, EmpName
       1, Yagga Boshu
External - ExternalEmpID, ExternalDeptName
           1 , Capegemini
Position - EmpID, Dept  P/S
           1, IT        Primary
           1, Finance   Secondary

我想以以下格式创建视图：

   Emp ID, EmpName, ExternalID, ExternalName, EmpPrimaryDept, EmpSecondaryDept
   1,     Yagga Boshu,  1 ,      CapeGemini  ,  IT,           FINANCE

1. empid和empname来自核心表。
2. externalId和externalname来自外部表，核心表
oneetoone
3. empprimarydept和empecondarydept来自位置表是带有核心表的OneTomany

for 1(和2(查询工作：

select * from CORE ec, EXTERNAL ee
where ec.EMPLOYEEID = ee.ExternalEmpID

如何加入位置表，以便如上所述的一行返回？

• 核心有20,000名员工数据。
• 在您看到1个emp ID的问题中，这些是两个表之间的链接。
• 我正在使用Oracle数据库12C版本12.1.0.1.0-64位生产

Answer 1

一种方法是在select子句中使用子征物：

select
  c.empid, c.empname, e.externaldeptname,
  (select dept from position p where p.empid = c.empid and p.p_s = 'Primary') as dept1,
  (select dept from position p where p.empid = c.empid and p.p_s = 'Secondary') as dept2
from core c
left join external e on e.externalempid = c.empid
order by c.empid;

您也可以将它们移至from子句：

select
  c.empid, c.empname, e.externaldeptname, p.dept as dept1, s.dept as dept2
from core c
left join external e on e.externalempid = c.empid,
left join (select empid, dept from position where p_s = 'Primary') p on p.empid = c.empid
left join (select empid, dept from position where p_s = 'Secondary') s on s.empid = c.empid
order by c.empid;

Answer 2

也许在加入3张桌子之后，您只需要一个LISTAGG。

    select EmpID, ec.EmpName
   , ee.ExternalDeptName
       ee.Dept, 
         LISTAGG(p.dept,',') as 
        'PrimarySecondaryDepts.' from
        CORE ec join EXTERNAL ee 
       on ec.empid =ee.empid join 
       Position p
        ec.empid=p.empid group by empid, 
         ec.empname, 
       ee.ExternalDeptName,
       ee.Dept