Oracle多表员工薪资逻辑实现：无需连接emp2与emp3

现有三张存储员工信息的表，结构及数据如下：

原始表数据 #

表 emp1 #

empid dep salary
1     BI   100

表 emp2 #

empid dep salary
1     PS   200
2     PS   null

表 emp3 #

empid dep salary
1     Sales   300
2     Sales   400

执行以下UNION查询后得到基础合并结果：

select * from emp1 union
select * from emp2 union
select * from emp3

查询结果：

empid dep salary
1     BI   100
1     PS   200
1     Sales 300
2     PS    null
2     Sales 400   

需求逻辑 #

需要对合并后的结果按以下规则调整薪资显示：

• 若员工属于dep=BI，直接显示BI对应的薪资；
• 若员工属于dep=Sales，检查该员工是否同时属于PS部门：若属于且PS部门薪资不为null，则PS和Sales行均显示PS的薪资；

要求：无需连接emp2与emp3实现该逻辑。

解决方案 #

可以通过CTE合并数据后，结合窗口函数实现需求，SQL语句如下：

WITH combined_data AS (
    SELECT * FROM emp1
    UNION
    SELECT * FROM emp2
    UNION
    SELECT * FROM emp3
)
SELECT
    empid,
    dep,
    CASE
        -- BI部门直接保留原薪资
        WHEN dep = 'BI' THEN salary
        -- Sales部门优先取同员工PS部门的非空薪资，无则保留原薪资
        WHEN dep = 'Sales' THEN COALESCE(MAX(CASE WHEN dep = 'PS' THEN salary END) OVER (PARTITION BY empid), salary)
        -- PS部门保留原薪资，当PS薪资非空时，Sales部门会同步使用该值
        ELSE salary
    END AS salary
FROM combined_data
ORDER BY empid, dep;

逻辑说明 #

1. 先用CTEcombined_data合并三张表的所有数据；
2. 针对每个员工（按empid分区），用窗口函数MAX(CASE WHEN dep = 'PS' THEN salary END)提取该员工PS部门的非空薪资；
3. 通过CASE语句分支处理：
   • BI部门直接返回原薪资；
   • Sales部门用COALESCE判断，若存在PS部门的非空薪资则替换，否则保留原薪资；
   • PS部门保持原薪资不变，当该薪资非空时，同员工的Sales部门行会自动同步此值。

执行上述查询后，得到的结果为：

empid dep salary
1     BI   100
1     PS   200
1     Sales 200
2     PS    null
2     Sales 400   

内容的提问来源于stack exchange，提问作者user3675044