本文介绍了我如何...我们如何计算两个表的总和的差异....的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 亲爱的先生 我有两个sql表Table1和Table2有以下数据: Table1 Dear sir I have two sql table Table1 and Table2 which have the following data:Table1ID ItemNameQuantity1 Collar1002 freeze2003 collar4004 freeze2005 tv2006 tv500 Table2 Table2ID ItemNameQuantity1 Collar702 collar603 tv2004 freeze505 tv906 freeze300 我想找到两个表的数量总和的差异。并且想要计算如下: 结果 I want to find the difference of Sum of Quantity of both tables. And want to calculate like:ResultItemNameQuantityCollar370freeze50tv410 请帮助执行sql查询以解决此问题 谢谢提前Please help to execute sql query for solving this problem Thanks advance推荐答案 SELECT ItemName, SUM(tb1total-tb2total) AS DIFFfrom( 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) tgroup by ItemName ; 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 QuantityFROM table1grouped t1gLEFT JOIN table2grouped t2g on t1g.ItemName = t2g.ItemName select sub.ItemName, SUM(sub.Quatity) from (select Table1.ItemName ,SUM(Table1.Quantity)Quatity from Table1group by Table1.ItemNameunionselect Table2.ItemName ,SUM(Table2.Quantity)*-1 Quatity from Table2group by Table2.ItemName) sub group by ItemName 这篇关于我如何...我们如何计算两个表的总和的差异....的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
10-26 19:22