将两个 SQL 语句与 sum 组合在一起

Question

两个语句需要组合。

select INVOICEAMOUNT, itemid 
from MTS_NONPAYMENT
select SUM(AMOUNT) AS SUM, ITEMID 
from CUS_GLACCOUNT 

常用列itemid .每次我尝试加入时，它都会失败。我做错了什么？

Answer 1

从根本上说，您似乎缺少group by . 我怀疑以下内容可以满足您的需求：

select itemid, sum(invoiceamount) as invoiceamount, sum(sum) as sum
from ((select itemid, sum(INVOICEAMOUNT) as invoiceamount, 0 as sum
       from MTS_NONPAYMENT
       group by itemid
      ) union all
      (select itemid, 0, SUM(AMOUNT)
       from CUS_GLACCOUNT 
       group by itemid
      ) 
     ) x
group by itemid;

要获取不相等的值，请使用：

having sum(invoiceamount) <> sum(sum) 

在查询结束时。