sql - 关于聚合的问题

Question

我需要找到总交货数量等于总销售数量的产品名称（iname）

给出的两个表是

sale:
snum:sqty:iname
1   :3   :Shoes
2   :1   :Shoes
3   :1   :Hat        

delivery:
dnum:dqty:iname
1   :5   :Shoes
2   :1   :Hat
3   :3   :Shoes

我尝试了此代码，但我得到的结果汇总不正确，因为同一商品有多个销售和交付

select sale.iname
from sale
inner join delivery
on sale.iname = delivery.iname
group by sale.iname
having sum(delivery.dqty) - sum(sale.sqty) = 0

当我执行以下操作时，我能够找到正确的总量，但是有没有办法可以在一个代码中做到这一点？

select iname, sum(sqty)
from sale
group by iname

select iname, sum(dqty)
from delivery
group by iname

标签： sql

Answer 1

你可以试试下面 -

select a.iname,saleQty,deliveryQty
from
(
select iname, sum(sqty) as saleQty
from sale
group by iname
)A inner join 
(
select iname, sum(dqty) as deliveryQty
from delivery
group by iname
)B on A.iname=B.iname where saleQty-deliveryQty=0