我们可以尝试使用条件聚合沿着解析函数：

WITH cte AS (
    SELECT t.*,
        MAX(CASE WHEN dep = 'PS' THEN salary END) OVER (PARTITION BY empid) AS dep_salary
    FROM yourTable t
)

SELECT empid, dep,
       CASE WHEN dep = 'Sales' AND dep_salary IS NOT NULL
            THEN dep_salary ELSE salary END AS salary
FROM cte;

您可以在CASE表达式中使用分析函数：

SELECT empid,
       dep,
       CASE
       WHEN dep = 'Sales'
       AND  COUNT(CASE WHEN dep = 'PS' THEN 1 END) OVER (PARTITION BY empid) > 0
       THEN SUM(CASE WHEN dep = 'PS' THEN salary END) OVER (PARTITION BY empid)
       ELSE salary
       END as salary
FROM   (
  SELECT empid, dep, salary FROM emp1 UNION ALL
  SELECT empid, dep, salary FROM emp2 UNION ALL
  SELECT empid, dep, salary FROM emp3
);

其中，对于样本数据：

CREATE TABLE emp1 (empid, dep, salary) AS
SELECT 1, 'BI', 100 FROM DUAL;

CREATE TABLE emp2 (empid, dep, salary) AS
SELECT 1, 'PS', 200 FROM DUAL;

CREATE TABLE emp3 (empid, dep, salary) AS
SELECT 1, 'Sales', 300 FROM DUAL;

输出：
| EMPID| DEP|工资|
| --|--|--|
| 1 |BI| 100 |
| 1 |销售| 200 |
| 1 |PS| 200 |
fiddle

其中一个选项是联合所有数据（以CTE形式执行），然后使用带有EXISTS子查询的Case表达式来检查DEP='Sales'的条件：

WITH        --  S a m p l e   d a t a :
    emp1 AS 
        (   Select 1 "EMPID", 'BI' "DEP", 100 "SALARY" From Dual    ),
    emp2 AS
        (   Select 1 "EMPID", 'PS' "DEP", 200 "SALARY" From Dual Union All
            Select 2 "EMPID", 'PS' "DEP", Null "SALARY" From Dual   ),
    emp3 AS
        (   Select 1 "EMPID", 'Sales' "DEP", 300 "SALARY" From Dual Union All
            Select 2 "EMPID", 'Sales' "DEP", 400 "SALARY" From Dual ),

...联合所有...

all_tabs AS
        (   Select * From emp1 Union All
            Select * From emp2 Union All
            Select * From emp3   )

...和main SQL，结果是：

--  M a i n    S Q L :
Select      t.EMPID, t.DEP,
            Case  When t.DEP = 'Sales' And EXISTS (Select 1 From all_tabs Where EMPID = t.EMPID And DEP = 'PS' And SALARY Is Not Null)
                Then (Select SALARY From all_tabs Where EMPID = t.EMPID And DEP = 'PS')
            Else t.SALARY
            End "SALARY"
From        all_tabs t
Order By    EMPID, DEP
--  
--  R e s u l t :
--       EMPID DEP       SALARY
--  ---------- ----- ----------
--           1 BI           100
--           1 PS           200
--           1 Sales        200
--           2 PS              
--           2 Sales        400