mysql - Sql查询选择table1的sum和table2的sum并相减

Question

Select idn, sum(tblppmp.total_item) as a_total
sum(tblRequest.Quantity) as b_total
sum(a_total- b_total) as itemsleft
FROM PPMP.dbo.tblppmp, ppmp.dbo.tblrequest
Group by idn

我有一个问题，如何对 table1 和 table2 中的各个项目求和，并将结果相减以获得答案。

像这样

table1
id     item
1        2
2        3
3        4

表2

id       item
1        1
2        2
3        3

我想要的结果是这样的..

表3

sum(table.item) - sum(table2.item)

table1.id 1 = 2
table2.id 1 = 1
so (2-1) = 1

id     item_left
1      1
2      1
3      1

id 项目

Answer 1

您可以先计算每个表的总和，然后再减去它们。

select  idn, 
        a_total - ISNULL(r_total,0) as itemsleft
FROM
(
    select idn, sum(p.total_item) as p_total
    from    PPMP.dbo.tblppmp p
    group by idn
) p
LEFT JOIN 
(
    select idn, sum(r.Quantity) as r_total
    from    ppmp.dbo.tblrequest r
    group by idn
) r on p.idn = r.idn