我如何...我们如何计算两个表的总和的差异.... [英] How do I...how we can calculate the difference of sum of two tables....

问题描述

亲爱的先生

我有两个sql表Table1和Table2有以下数据：



Table1

Dear sir
I have two sql table Table1 and Table2 which have the following data:

Table1

ID	 ItemName	Quantity
1	 Collar		100
2	 freeze		200
3	 collar		400
4	 freeze		200
5	 tv		200
6	 tv		500

Table2

Table2

ID	 ItemName	Quantity
1	 Collar		70
2	 collar		60
3	 tv		200
4	 freeze		50
5	 tv		90
6	 freeze		300

我想找到两个表的数量总和的差异。并且想要计算如下：



结果

I want to find the difference of Sum of Quantity of both tables. And want to calculate like:

Result

ItemName	Quantity
Collar		370
freeze		50
tv		410

请帮助执行sql查询以解决此问题

谢谢提前

Please help to execute sql query for solving this problem
Thanks advance

推荐答案

这里我根据您的要求创建了新查询

Here I created new query and according to your requirement

SELECT ItemName, SUM(tb1total-tb2total) AS DIFF
from
(
    select ItemName,sum(Quantity) as tb1total,0 as tb2total
    from Table1 group by ItemName
    union all
    select ItemName,0 as tb1total, sum(Quantity) as tb2total
    from Table2 group by ItemName
) t
group by ItemName

我更喜欢使用cte而不是临时表< br $> b $ b

i prefer using cte instead of temporary table

; with table1grouped as (
    SELECT ItemName,Sum(Quantity) as Quantity
    FROM table1
    GROUP BY itemName
), table2grouped as (
    SELECT ItemName,Sum(Quantity) as Quantity
    FROM table2
    GROUP BY itemName
)
SELECT ItemName, (t1g.Quantity - ISNULL(t2g.Quantity,0)) as Quantity
FROM table1grouped t1g
LEFT JOIN table2grouped t2g on t1g.ItemName = t2g.ItemName

select  sub.ItemName, SUM(sub.Quatity) from (select Table1.ItemName ,SUM(Table1.Quantity)Quatity from Table1
group by  Table1.ItemName
union
select Table2.ItemName ,SUM(Table2.Quantity)*-1 Quatity from Table2
group by  Table2.ItemName) sub group by ItemName

这篇关于我如何...我们如何计算两个表的总和的差异....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！