我想将这两个选择查询的结果连接为一个表中的四列

Question

select 
    SUM (cp.TotalAmount) as totalPaymentamount,
    lvl4.SubSubsidaryAccountName as account1
from 
    TBLCPVMaster cp,TBLLevel4 lvl4
where 
    cp.SubSubsidaryAccountId = lvl4.SubSubsidaryAccountCode  
group by 
    lvl4.SubSubsidaryAccountName
select 
    SUM (cr.TotalAmount) as totalReciveamount,
    lvl4_2.SubSubsidaryAccountName as account2
from 
    TBLCRVMaster cr, TBLLevel4 lvl4_2
where 
    cr.SubSubsidaryAccountId = lvl4_2.SubSubsidaryAccountCode 
group by 
    lvl4_2.SubSubsidaryAccountName 

生成的表应有 4 列...请帮忙

提前致谢

Answer 1

您确实应该开始使用 ANSI-92 中的连接语法。已经25年了。

with Payments as
(
    select SUM (cp.TotalAmount) as totalPaymentamount
        ,   lvl4.SubSubsidaryAccountName as account1
    from TBLCPVMaster cp
    join TBLLevel4 lvl4 on cp.SubSubsidaryAccountId = lvl4.SubSubsidaryAccountCode 
    group by lvl4.SubSubsidaryAccountName
)
, Receipts as
(
    select SUM (cr.TotalAmount) as totalReciveamount
        , lvl4_2.SubSubsidaryAccountName as account2
    from TBLCRVMaster cr
    join TBLLevel4 lvl4_2 on cr.SubSubsidaryAccountId = lvl4_2.SubSubsidaryAccountCode 
    group by lvl4_2.SubSubsidaryAccountName 
)
select p.totalPaymentAmount
    , p.account1
    , r.totalReciveAmount
    , r.account2
from Payments p
cross join Receipts r

Answer 2

我只是对我认为您要实现的目标进行最简单的理解......获取每个帐户的付款总额和收到的总额。

select 
    lvl4.SubSubsidaryAccountName, -- surely you only need name once?
    SUM (cp.TotalAmount) as totalPaymentamount,
    SUM (cr.TotalAmount) as totalReciveamount
from 
    TBLLevel4 lvl4
    left join TBLCPVMaster cp 
        on cp.SubSubsidaryAccountId=lvl4.SubSubsidaryAccountCode
    left join TBLCRVMaster cr
        on cr.SubSubsidaryAccountId=lvl4_2.SubSubsidaryAccountCode
group by lvl4.SubSubsidaryAccountName