在Oracle中按子句标题将伪行显示为组

Question

我有三个表，分别是公司、部门和员工。现在，我想找出在D部门A公司工作的所有员工，并希望将数据显示如下。

所有这些表之间存在父密钥关系

**TABLE COMPANY**
COMPANY_NAME  COMPANY ID
C1            COMP1
C2            COMP2
C3            COMP3
**TABLE DEPARTMENT**
DEPARTMENT_NAME    COMPANY_NAME
D1                C1
D2                C1
D3                C2
**TABLE EMPLOYEE**
EMPLOYEE_ID   DEPARTMENT_NAME
E1              D1
E2              D1
E3              D1
E4              D2
E5              D2
Company -- > Department --- > Employee.

我还想将每个列的实体显示为虚列。

ENTITY     COMPANY_NAME DEPARTMENT_NAME EMPLOYEE_ID
COMPANY      C1           -              -
DEPARTMENT   C1           D1               
EMPLOYEE     C1           D1             E1
EMPLOYEE     C1           D1             E2
EMPLOYEE     C1           D1             E3
DEPARTMENT   C1           D2             -
EMPLOYEE     C1           D2             E4
EMPLOYEE     C1           D2             E5  ``

我已经用groupby尝试过了，但用groupby我得到了垂直树状结构的数据。但我想要的是，应该有一个类似公司或部门的标题，在这个标题下，该部门的员工应该显示出来。有没有可能的方法可以在sql或plsql中做到这一点？

用于左侧外部连接

ENTITY     COMPANY_NAME DEPARTMENT_NAME EMPLOYEE_ID
COMPANY      C1           -              -
DEPARTMENT   C1           D1               
EMPLOYEE     C1           D1             E1
EMPLOYEE     C1           D1             E2
EMPLOYEE     C1           D1             E3
DEPARTMENT   C1           D2             -
EMPLOYEE     C1           D2             E4
EMPLOYEE     C1           D2             E5 
COMPANY      C2           -              -
DEPARTMENT   C2           D3             -
COMPANY      C3           -              -

Answer 1

您可以使用聚合和grouping sets来执行此操作。这看起来像：

select (case when grouping(employee_id) = 0 then 'EMPLOYEE'
when grouping(department_name) = 0 then 'DEPARTMENT'
else 'COMPANY'
end) as entity,
company_name, department_name, e.employee_id
from company c join
department d
using (company_name) join
employee e
using (department_name)
group by grouping sets ( (company_name, department_name, e.employee_id), (company_name, department_name), (company_name) )
order by company_name, department_name nulls first, employee_id nulls first;

这里有一个db<gt；不停摆弄

Answer 2

您可以按如下方式使用UNION ALL：

SELECT COMPANY_NAME, NULL AS DEPARTMENT_NAME, NULL AS EMPLOYEE_ID 
FROM COMPANY C 
WHERE EXISTS (SELECT 1 FROM EMPLOYEE E JOIN DEPARTMENT D USING (DEPARTMENT_NAME) 
WHERE C.COMPANY_NAME = D.COMPANY_NAME)
UNION ALL
SELECT COMPANY_NAME, DEPARTMENT_NAME, NULL AS EMPLOYEE_ID 
FROM COMAPNY C JOIN DEPARTMENT D USING (COMPANY_NAME)
WHERE EXISTS (SELECT 1 FROM EMPLOYEE E 
WHERE E.DEPARTMENT_NAME = D.DEPARTMENT_NAME)
UNION ALL
SELECT COMPANY_NAME, DEPARTMENT_NAME, EMPLOYEE_ID 
FROM EMPLOYEE JOIN DEPARTMENT USING (DEPARTMENT_NAME)
ORDER BY COMPANY_NAME, 
DEPARTMENT_NAME NULLS FIRST, 
EMPLOYEE_ID NULLS FIRST;