左外部結合に向かうことにより、ダミー行をグループとして表示します

Nov 24 2020

会社、部門、従業員の3つのテーブルがあります。ここで、A社でD部門の下で働いているすべての従業員を調べ、内部結合と左外部結合のデータを表示して、従業員がいない部門と部門がない会社を確認したいと思います。

このすべてのテーブル間に親キーの関係があります

**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 
    COMPANY      C2           -              -
    DEPARTMENT   C2           D3             -
    COMPANY      C3           -              -

回答

Popeye Nov 24 2020 at 06:54

SET演算子UNION ALLは次のように使用できます。

SELECT 'COMPANY' AS ENTITY, 
        COMPANY_NAME, 
        '-' AS DEPARTMENT_NAME, 
        '-' AS EMPLOYEE_ID 
   FROM COMPANY C
UNION ALL
SELECT 'DEPARTMENT' AS ENTITY, 
        C.COMPANY_NAME, 
        D.DEPARTMENT_NAME, 
        '-' AS EMPLOYEE_ID 
   FROM DEPARTMENT D 
   JOIN COMPANY C ON C.COMPANY_ID = D.COMPANY_ID
UNION ALL
SELECT 'EMPLOYEE' AS ENTITY, 
        C.COMPANY_NAME, 
        D.DEPARTMENT_NAME, 
        E.EMPLOYEE_ID 
  FROM DEPARTMENT D 
  JOIN COMPANY C ON C.COMPANY_ID = D.COMPANY_ID
  JOIN EMPLOYEE E ON E.DEPARTMENT_NAME = D.DEPARTMENT_NAME
ORDER BY COMPANY_NAME, 
         CASE WHEN DEPARTMENT_NAME = '-' THEN 1 ELSE 2 END, 
         DEPARTMENT_NAME,
         CASE WHEN EMPLOYEE_ID = '-' THEN 1 ELSE 2 END, 
         EMPLOYEE_ID
gogocho Nov 24 2020 at 08:53

これを試してください:

select
    case entity_disp
        when 1 then 'COMPANY'
        when 2 then 'DEPARTMENT'
        when 3 then 'EMPLOYEE'
    end as entity,
    company_name,
    department_name,
    employee_id
    from
    
(select
    c.company_name,
    d.department_name,
    e.employee_id,
    1 + decode(d.department_name, null, 0, 1) + decode(e.employee_id, null, 0, 1) as entity_disp
from
    company c
    left outer join department d on (d.company_name = c.company_name)
    left outer join employee  e on (e.department_name = d.department_name)
 ) c   
 order by c.entity_disp

yor tabelモデルの例によれば、CompanyとDepartmentのtabelsはcompany_nameに基づいて結合されているように見えることを考慮してください。また、DepartmentテーブルとEmployeeテーブルはDepartment_nameに基づいて結合されます。これがselectの実装方法です。必要に応じて-適切なリレーショナル列を使用して結合をやり直します。