sql - ORACLE SQL：组合 2 行 2 个单独的选择查询

Question

你好，我对 SQL 很陌生，我有一个问题。我有 3 张桌子的工作，收入和成本。job.unid = cost.job_unid 和收入.job_unid

我想找到每个工作的总收入和成本，然后在一行上找到每个工作的利润：

标题看起来像这样： 工作编号、总收入、总成本、损益（Rev-Cost）

我尝试进行单个查询，但在互联网上搜索解决方案后，我似乎应该分别查询收入和成本，然后使用 Union All 合并。

现在我正在这样做：

  select m,
rlc,
rvatlc,
rtotlc,
clc,
cvatlc,
ctotlc
from(

(select decode(j.consolno,null,j.shpno,j.consolno) m,

sum(nvl(r.amtlc,0)) rlc,
sum(nvl(r.vatamtlc,0)) rvatlc,
sum(nvl(r.amtlc,0))+sum(nvl(r.vatamtlc,0)) rtotlc,
0 clc,
0 cvatlc,
0 ctotlc


from 
job j
full join revenue r on j.unid=r.job_unid
where j.voidby is  null and decode(j.consolno,null,j.shpno,j.consolno)='SHSEM00131'
group by decode(j.consolno,null,j.shpno,j.consolno)
)







union all




(select decode(j.consolno,null,j.shpno,j.consolno) m,


0 rlc,
0 rvatlc,
0 rtotlc,
sum(nvl(c.amtlc,0)) clc,
sum(nvl(c.vatamtlc,0)) cvatlc,
sum(nvl(c.amtlc,0))+sum(nvl(c.vatamtlc,0)) ctotlc


from 
job j, cost c 
where j.voidby is  null and j.unid=c.job_unid and decode(j.consolno,null,j.shpno,j.consolno)='SHSEM00131'
group by decode(j.consolno,null,j.shpno,j.consolno)
))

结果是： 我不确定你是否能看到，但它是 2 行：1 是收入，1 是成本。
我想结合这两条线

查询结果.jpg

标签： sqloracleaggregate-functionsunion-all

Answer 1

尝试这个。

   select m,
    sum(rlc),
    sum(rvatlc),
    sum(rtotlc),
    sum(clc),
    sum(cvatlc),
    sum(ctotlc)
    from(

    (select decode(j.consolno,null,j.shpno,j.consolno) m,

    sum(nvl(r.amtlc,0)) rlc,
    sum(nvl(r.vatamtlc,0)) rvatlc,
    sum(nvl(r.amtlc,0))+sum(nvl(r.vatamtlc,0)) rtotlc,
    0 clc,
    0 cvatlc,
    0 ctotlc


from 
job j
full join revenue r on j.unid=r.job_unid
where j.voidby is  null and decode(j.consolno,null,j.shpno,j.consolno)='SHSEM00131'
group by decode(j.consolno,null,j.shpno,j.consolno)
)

union all

(select decode(j.consolno,null,j.shpno,j.consolno) m,


0 rlc,
0 rvatlc,
0 rtotlc,
sum(nvl(c.amtlc,0)) clc,
sum(nvl(c.vatamtlc,0)) cvatlc,
sum(nvl(c.amtlc,0))+sum(nvl(c.vatamtlc,0)) ctotlc


from 
job j, cost c 
where j.voidby is  null and j.unid=c.job_unid and decode(j.consolno,null,j.shpno,j.consolno)='SHSEM00131'
group by decode(j.consolno,null,j.shpno,j.consolno)
)) group by m;