如何从没有常用值的不同表创建oracle视图

I have the following tables

我有以下表格

COMPANY

 =========================
 |COMPANY_ID|COMPANY_NAME|
 =========================
 |      C1  |  Test1     | 
 |      C2  |  Test2     |
 =========================

DEPARTMENT

 ========================
 |DEPT_ID   | DEPT_NAME |  
 ========================
 |     D1   |  Sales    | 
 |     D2   |  HR       |
 ========================

COMPANY_DEPARTMENT_PROFILE

 ====================================================
 |PROFILE_ID| DEPT_ID   | COMPANY_ID | PROFILE_VALUE|
 ====================================================
 |       1  |  D1       |    C1      |  ACTIVE      |
 |       2  |  D2       |    C1      |  INACTIVE    |
 ====================================================

DEFAULT_PROFILE

 ========================================
 |DEFAULT_ID| DEPT_ID   |  PROFILE_VALUE|
 ========================================
 |       1  |  D1       |    ACTIVE     |
 |       2  |  D2       |    ACTIVE     |
 ========================================

The table logic is such that we maintain a table of available companies and departments which are not dependent on each other. Let's say all the companies in COMPANY table has all the departments in DEPARTMENT table.

表逻辑是这样的,我们维护一个可用的公司和部门的表,这些表并不相互依赖。假设COMPANY表中的所有公司都有DEPARTMENT表中的所有部门。

COMPANY_DEPARTMENT_PROFILE has the company related, department profile information. This table may or may not have data for each company. In this example table, the COMPANY_DEPARTMENT_PROFILE has only entries for company C1 and not C2.

COMPANY_DEPARTMENT_PROFILE具有公司相关的部门配置文件信息。该表可能包含也可能没有每个公司的数据。在此示例表中,COMPANY_DEPARTMENT_PROFILE仅包含公司C1的条目,而不包含C2。

DEFAULT_PROFILE contains the default profile value for each department irrespective of company. This data can be overridden by value in COMPANY_DEPARTMENT_PROFILE for a specific company.

DEFAULT_PROFILE包含每个部门的默认配置文件值,与公司无关。对于特定公司,可以通过COMPANY_DEPARTMENT_PROFILE中的值覆盖此数据。

Now I need to create a view in the following format

现在我需要以下面的格式创建一个视图

=============================================
|COMPANY_ID|DEPT_ID|PROFILE_ID|PROFILE_VALUE|
=============================================
|    C1    |  D1   |    1     |   ACTIVE    |
|    C1    |  D2   |    2     |   INVACTIVE |
|    C2    |  D1   |    1     |   ACTIVE    |
|    C2    |  D2   |    2     |   ACTIVE    |
=============================================

The logic is I need to create a view with profile value for each department for each company. If the company has profile value already in the COMPANY_DEPARTMENT_PROFILE, we need to take values from there. But if the company does not have any entry in the COMPANY_DEPARTMENT_PROFILE then we need to populate the default values for that department from DEFAULT_PROFILE.

逻辑是我需要为每个公司的每个部门创建一个包含配置文件值的视图。如果公司已在COMPANY_DEPARTMENT_PROFILE中具有配置文件值,我们需要从那里获取值。但是如果公司在COMPANY_DEPARTMENT_PROFILE中没有任何条目,那么我们需要从DEFAULT_PROFILE填充该部门的默认值。

2 个解决方案

#1

You can generate all the companies and departments using a cross join. Then bring in the additional profile information using left join and some logic to choose the default:

您可以使用交叉联接生成所有公司和部门。然后使用左连接和一些逻辑来引入其他配置文件信息以选择默认值:

select c.company_id, d.dept_id,
       coalesce(cdp.profile_id, dp.profile_id) as profile_id,
       (case when cdp.profile_id is not null then cdp.profile_value else dp.profile_value end) as profile_value
from company c cross join
     department d left join
     company_department_profile cdp
     on cdp.company_id = c.company_id and cdp.dept_id = cdp.dept_id left join
     default_profile dp
     on d.dept_id = dp.dept_id;

#2

Isn't it what you're looking for?

这不是你想要的吗?

SQL> with
  2    company as
  3      (select 'C1' company_id, 'Test 1' company_name from dual
  4       union all
  5       select 'C2', 'Test 2' from dual),
  6    department as
  7      (select 'D1' dept_id, 'Sales' dept_name from dual
  8       union all
  9       select 'D2', 'HR' from dual),
 10    company_department_profile as
 11      (select 1 profile_id, 'D1' dept_id, 'C1' company_id, 'ACTIVE' profile_value from dual
 12       union all
 13       select 2, 'D2', 'C1', 'INACTIVE' from dual),
 14    default_profile as
 15      (select 1 default_id, 'D1' dept_id, 'ACTIVE' profile_value from dual
 16       union all
 17       select 2, 'D2', 'ACTIVE' from dual)
 18  select
 19    c.company_id,
 20    d.dept_id,
 21    coalesce(cdp.profile_id, dp.default_id) profile_id,
 22    coalesce(cdp.profile_value, dp.profile_value) profile_value
 23  from
 24    company c
 25      cross join
 26    department d
 27      join
 28    default_profile dp
 29      on (dp.dept_id = d.dept_id)
 30      left outer join
 31    company_department_profile cdp
 32      on (cdp.company_id = c.company_id and cdp.dept_id = d.dept_id);

COMPANY_ID DEPT_ID  PROFILE_ID PROFILE_VALUE
---------- -------- ---------- --------------------------------
C1         D1                1 ACTIVE
C1         D2                2 INACTIVE
C2         D2                2 ACTIVE
C2         D1                1 ACTIVE

#1