我有两张桌子a和b。公司名称，产品名称相同。我该怎么做？
我有两张桌子a和b。公司名称，产品名称相同。我该怎么做？
提问于 2021-06-01 14:01:57
table_a
.........................................
cmp_name pro_name purchase
a x1 5
a x2 7
b p1 8
a x1 8
b p2 12
b p1 6
a x1 3
table_b
.........................................
cmp_name pro_name sale
a x1 3
a x2 1
b p1 4
a x1 3
b p2 2
b p1 4
a x1 1
result
.........................................
cmp_name pro_name Total_pur Total_sale stock
a x1 16 7 9
a x2 7 1 6
b p1 14 8 6
b p2 12 2 10我该怎么做呢？
回答 2
Stack Overflow用户
发布于 2021-06-01 15:07:30
尝试下面的查询，但是您必须有一个更好的设计来处理这种情况
SELECT
table_a.cmp_name,
table_a.pro_name c,
(SELECT
SUM(table_a.purchase)
FROM
table_a
WHERE
table_a.pro_name = c) AS Total_pur,
(SELECT
SUM(table_b.sale)
FROM
table_b
WHERE
table_b.pro_name = c) AS Total_sale,
((SELECT
SUM(table_a.purchase)
FROM
table_a
WHERE
table_a.pro_name = c) - (SELECT
SUM(table_b.sale)
FROM
table_b
WHERE
table_b.pro_name = c)) AS stock
FROM
table_a
LEFT JOIN
table_b ON table_a.cmp_name = table_b.cmp_name
GROUP BY table_a.cmp_name , table_a.pro_nameStack Overflow用户
发布于 2021-06-01 16:22:15
您需要同时对purchase和sale表执行按cmp_name和pro_name分组。这是通过cmp_name和pro_name获得总的购买和销售
之后使用purchase表LEFT JOIN到sale表。(因为你可能有购买但没有出售的物品)
select a.cmp_name,
a.pro_name,
a.purchase,
b.sale,
stock = a.purchase - isnull(b.sale, 0)
from (
select cmp_name, pro_name, purchase = sum(purchase)
from table_a
group by cmp_name, pro_name
) a
left join
(
select cmp_name, pro_name, sale = sum(sale)
from table_b
group by cmp_name, pro_name
) b on a.cmp_name = b.cmp_name
and a.pro_name = b.pro_name
order by a.cmp_name, a.pro_name页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67783425
复制相关文章
相似问题
腾讯云开发者
