sql查询添加原始查询中缺少的行

lf3rwulv  于 2021-07-26  发布在  Java
关注(0)|答案(4)|浏览(322)

我创建了一个查询来获取工资单运行结果-

Query #1-
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

现在,这个查询的输出如下-

employeenumber      effective_date      currentamount   UNITS       job_name        element name                    payroll_action_id
    100             2020-07-03          200             1           Supervisor      Meals 10 Earnings Results       456
    100             2020-05-08          100             2           Supervisor      Meals 10 Earnings Results       574

另一个表将包含此员工的工资余额。这将包括相同的currentamount,比如-200100和上面查询中未包含的那些。

Payroll_balance table - 

    PERSON_NUMBER   BALANCE_NAME    BALANCE_VALUE   EFFECTIVE_DATE      payroll_action_id               
    100             Meals Units     200             2020-07-03          456 
    100             Meals Units     100             2020-05-08          574
    100             Meals Units     350             2020-01-01          578

payroll\u action\u id在这两个表之间是公共的。未包含在查询#1(578)中的payroll#u actionŦid应使用payrollŦu balance query添加

Expected output - 

 employeenumber     effective_date      currentamount   UNITS       job_name        element name                    payroll_action_id
    100             2020-07-03          200             1           Supervisor      Meals 10 Earnings Results       456
    100             2020-05-08          100             2           Supervisor      Meals 10 Earnings Results       574
    100             2020-01-01          350                         Supervisor      Meals Units                     578

How can this be achieved ?
f0brbegy

f0brbegy1#

获得预期输出的一种方法是 LEFT JOINNVL 检查每一列。左连接将确保您考虑 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
3xiyfsfu

3xiyfsfu2#

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表的匹配行。

mv1qrgav

mv1qrgav3#

只有两个共同的想法:

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
hpcdzsge

hpcdzsge4#

假设第二个表是,第一个表是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

相关问题