获得预期输出的一种方法是 LEFT JOIN 与 NVL 检查每一列。左连接将确保您考虑 payroll_balance table。 NVL 将用中的实际值替换空值 payroll_balance table。
为了演示，我已经解释了前四列（每种类型一列）。请检查下面的sql并扩展解决方案。然而，我不清楚聚合逻辑与否 GROUP BY 或者 WINDOW 原始sql中的子句。

SELECT  
       nvl(x.employeenumber,p pb.erson_number), 
       nvl(x.effective_date, p.effective_date),  
       Case when x.employeenumber is not null then Sum(currentamount) else sum(balance_value) end AS currentamount,  
       nvl(x.payroll_action_id, pb.payroll_action_id)
FROM   payroll_balance pb
Left join xyz x on pb.payroll_action_id = x.payroll_action_id

SELECT B.*,A.*
FROM
(SELECT  
       employeenumber, 
       effective_date, 
       classificationname, 
       SUM(currentamount) AS currentamount,  
       CASE 
         WHEN earnings LIKE '%Meals 10 Earnings%' THEN NULL 
         ELSE Sum(hours_value) 
       END                hours, 
       AVG(rate)          Rate, 
       CASE 
         WHEN earnings LIKE '%Meals 10 Earnings%' 
         THEN Sum(hours_value) 
         ELSE NULL 
       END                UNITS, 
       job_name,
       payroll_action_id
FROM   xyz 
GROUP BY  employeenumber, 
       effective_date, 
       classificationname, 
       CASE 
         WHEN earnings LIKE '%Meals 10 Earnings%' THEN NULL 
         ELSE Sum(hours_value) 
       END,              
       CASE 
         WHEN earnings LIKE '%Meals 10 Earnings%' 
         THEN Sum(hours_value) 
         ELSE NULL 
       END, 
       job_name,
       payroll_action_id) A
RIGHT JOIN Payroll_balance B ON A.payroll_action_id=B.payroll_action_id

这是一个假定的查询，在这个查询中，您似乎需要来自payroll\u balance的所有值以及来自xyz表的匹配行。

只有两个共同的想法：

1通过union all

select * from <original query 1>
union all
select * from <all records from balance table that not exists in query1 >

类似于下面的查询，但实现的细节取决于实际数据

/*original "xyz" part*/   
select * from <query1>    
UNION ALL
/*second "balance" part*/
select PERSON_NUMBER as employeenumber, BALANCE_VALUE as currentamount, EFFECTIVE_DATE, null as UNITS
,... as job_name, BALANCE_NAME as element name
 from Payroll_balance
   where not exists 
(select * from xyz 
  where xyz.employeenumber =  Payroll_balance.PERSON_NUMBER
    and xyz.currentamount =  Payroll_balance.BALANCE_VALUE
    and xyz.effective_date =  Payroll_balance.effective_date
    and xyz.payroll_action_id = Payroll_balance.payroll_action_id
)

2通过完全外部连接

select nvl(xyz.employeenumbe, Payroll_balance.PERSON_NUMBER) as xyz.employeenumber
from <... query1...>
 full outer join Payroll_balance on 
xyz.employeenumber =  Payroll_balance.PERSON_NUMBER
        and xyz.currentamount =  Payroll_balance.BALANCE_VALUE
        and xyz.effective_date =  Payroll_balance.effective_date
        and xyz.payroll_action_id = Payroll_balance.payroll_action_id

假设第二个表是，第一个表是xyz：

Select coalesce(abc.PERSON_NUMBER, xyz.employeenumber) employee_number, 
abc.EFFECTIVE_DATE, abc.balance_value currentamount   
coalesce(xyz.job_name, abc.job_name)
coalesce(bc.BALANCE_NAME,xyz.element_name) element_name, 
payroll_action_id
from abc LEFT OUTER JOIN xyz ON abc.payroll_action_id = xyz.payroll_action_id