时间戳

首页 > 解决方案 > 在 oracle 中连接具有相同列的两个查询的结果

sql - 在 oracle 中连接具有相同列的两个查询的结果

问题描述

我正在使用甲骨文。我目前正在处理具有两个不同查询输出的表。我想将两个输出合并为单个输出，我尝试过 Union all 和 union 但没有运气。

with D as 
(
Select 
VP.HOMELABORLEVELNM4 as DEPT,
SUM(X.DURATIONSECSQTY/3600.0) as ACTL_HR,
SUM(X.WAGEAMT) as ACTL_DLR,
to_char(X.APPLYDTM,'YYYY-MM') AS MONTHLY,
VP.HOMELABORLEVELDSC4 as DESCRIPTION,
NULL as DAILY,
NULL as DEPT1,
NULL as ACTL_HR1,
NULL as ACTL_DLR1
from VP_EMPLOYEEV42 VP,
WFCTOTAL X
where 
VP.PERSONID = X.EMPLOYEEID and
X.APPLYDTM between '01-DEC-18' and '31-DEC-18' and
X.EMPLOYEEID in (select personid from PERSONCSTMDATA where CUSTOMDATADEFID ='154' and PERSONCSTMDATATXT = 'USKEANE')
group by VP.HOMELABORLEVELNM4, VP.HOMELABORLEVELDSC4, to_char(X.APPLYDTM,'YYYY-MM')
union all
Select 
NULL as DEPT,
NULL as ACTL_HR,
NULL as ACTL_DLR,
NULL as MONTHLY,
VP.HOMELABORLEVELDSC4 as DESCRIPTION,
to_char(X.APPLYDTM) as DAILY,
VP.HOMELABORLEVELNM4 as DEPT1,
SUM(X.DURATIONSECSQTY/3600.0) as ACTL_HR1,
SUM(X.WAGEAMT) as ACTL_DLR1
from VP_EMPLOYEEV42 VP,
WFCTOTAL X
where 
VP.PERSONID = X.EMPLOYEEID and
X.APPLYDTM = '31-DEC-18' and
X.EMPLOYEEID in (select personid from PERSONCSTMDATA where CUSTOMDATADEFID ='154' and PERSONCSTMDATATXT = 'USKEANE')
group by VP.HOMELABORLEVELNM4, VP.HOMELABORLEVELDSC4, to_char(X.APPLYDTM)
)
select D.DEPT DEPT,  
SUM(D.ACTL_HR) ACTL_HR, 
SUM(D.ACTL_DLR) ACTL_DLR,
D.MONTHLY MONTHLY, 
D.DESCRIPTION DESCRIPTION, 
D.DAILY DAILY, 
D.DEPT1 DEPT1, 
SUM(D.ACTL_HR1) ACTL_HR1, 
SUM(D.ACTL_DLR1) ACTL_DLR1
from D
group by D.DEPT, D.MONTHLY, D.DAILY, D.DESCRIPTION, D.DEPT1
order by DESCRIPTION

它给了我这样的输出

-部门-HR-DLR-每月-DESC-每日-部门-HR-DLR-

-1-12-12-11/1-Manu-NULL-NULL-NULL-NULL-

-NULL-NULL-NULL-NULL-Manu-17-1-12-12-

标签： sqloraclejoingroup-byunion

解决方案

我认为您的目标（我不太清楚）可能更容易按照这种模式实现：

with Query1 as (select fields from table where conditions are met),
     Query2 as (select fields from table where conditions are met) 
select  fields 
from Query1 
     outer join Query2 
     on Query1.identifier_for_match=Query2.identifier_for_match
where optional conditions are true

注意 - 在您的情况下，“identifier_for_match”可能是 employeeid（这将使其成为 Query1/Query2 结果集的必需部分） - 您必须查看模型并弄清楚查询应如何组合行。此外 - 如果提供表格的 DDL 以及相同的一些数据（包括所需的输出），则更容易提供适合您表格的答案

推荐阅读