一个左连接求和问题
select
sum(case when a.billtype=0 then b.outqty else 0 end) as delivery_outqty,
sum(case when a.billtype=0 then a.replaceamount else 0 end) as delivery_replaceamt
from
ex_express a
left join ex_expressdetail b on a.expressid=b.expressid
问题是如expressdetail有多行,第二个sum就成倍的加了。有什么办法能过滤的吗?二个表的主键分别是expressid和expressdetailid
------解决方案--------------------sum --> min/max
------解决方案--------------------select
(select sum(outq) From ex_expressdetail b Where b.expressid=a.expressid) as delivery_outqty,
sum(a.replaceamount) as delivery_replaceamt
from ex_express a
where a.billtype=0
------解决方案--------------------先聚合好再关联不就行了?
select
sum(case when a.billtype=0 then b.outqty else 0 end) as delivery_outqty,
sum(case when a.billtype=0 then a.replaceamount else 0 end) as delivery_replaceamt
from
ex_express a
left join
(select expressid,sum(outqty)outqty from ex_expressdetail group by expressid) b
on a.expressid=b.expressid