一个左连接求和问题
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
