当左连接两个表时，Sum似乎是双倍/三倍计数
提问于 2014-07-22 16:35:52
我试图通过一个标识号连接两个表(inventory和sales)，并将库存中的商品数量、库存值和销售额/销量相加。这是按零件代号分组的。
这就是我要做的：
`select inv.[num]
, sum(inv.[Value]) as [Inventory Value]
, sum(inv.[qty]) as [Inventory Qty]
, sum(sales.[Value]) as [Sales Value]
, sum(sales.[qty]) as [Sales qty]
from Inv
left join sales
on inv.[num]=sales.[num]
group by inv.[num]`库存价值总和大约是50倍大，销售额大约是3到4倍大。我怎样才能避免过度求和？
回答 1
Stack Overflow用户
发布于 2015-04-03 04:11:03
当将group by与多对一或多对多的连接一起使用时，您应该首先聚合度量并在维度上连接。
select
inv_agg.inv_num as 'Inventory Number'
,inv_agg.inv_value as 'Inventory Value'
,inv_agg.inv_qty as 'Inventory Qty'
,sales_agg.sales_value as 'Sales Value'
,sales_agg.sales_qty as 'Sales Qty'
from (select
inv.num as inv_num
,sum(inv.Value) as inv_value
,sum(inv.qty) as inv_qty
from inv
group by inv.num) inv_agg
left join (select
sales.num sales_num
,sum(sales.Value) as sales_value
,sum(sales.qty) as sales_qty
from sales
group by sales.num) sales_agg
on inv_agg.inv_num = sales_agg.sales_num或者，使用CTE
with inv_agg as (select
inv.num as inv_num
,sum(inv.Value) as inv_value
,sum(inv.qty) as inv_qty
from inv
group by inv.num),
sales_agg as (select
sales.num sales_num
,sum(sales.Value) as sales_value
,sum(sales.qty) as sales_qty
from sales
group by sales.num)
select
inv_agg.inv_num as 'Inventory Number'
,inv_agg.inv_value as 'Inventory Value'
,inv_agg.inv_qty as 'Inventory Qty'
,sales_agg.sales_value as 'Sales Value'
,sales_agg.sales_qty as 'Sales Qty'
from inv_agg left join sales_agg
on inv_agg.inv_num = sales_agg.sales_num页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24892979
复制相关文章
![SQL查询左连接、右连接、内连接[通俗易懂]](https://ask.qcloudimg.com/http-save/yehe-8223537/882120640027f87ea8828f58e27022d9.jpeg)
![mysql左连接查询慢[通俗易懂]](https://ask.qcloudimg.com/http-save/yehe-8223537/e3b222887bae9fce2bb71fd8030446be.png)
点击加载更多
腾讯云开发者
