mysql LEFT JOIN 使用SUM不准确

Question

原sql如下：

SELECT a.id, SUM(a.contractAmount) as contractAmount,SUM(b.kpprice) as invoiceAmount FROM contract AS a LEFT JOIN invoice as b on a.id=b.contractid where a.adminid = '1' GROUP BY a.issuingUnit,a.invoiceTypes

希望contract表中的contractAmount字段根据GROUP BY条件使用sum统计，并且invoice表中的kpprice字段根据条件a.id=b.contractid使用sum数据统计。

现在情况是 SUM(b.kpprice) 数据是正常的，但是SUM(a.contractAmount)出现了重复相加的情况 因为LEFT JOIN的on a.id=b.contractid是有多条的情况，还不能去重，这个应该怎么调整，求大神指教

Answer 1

left join的on 后面跟的字段应该跟 group by的字段一致，才能进行sum，否则会出现一对多的情况并且那个"多"不能合并成一，导致了多对一存在空白

可以试下改成这样

SELECT a.id, SUM(a.contractAmount) as contractAmount,SUM(b.kpprice) as invoiceAmount FROM contract AS a LEFT JOIN invoice as b on a.id=b.contractid where a.adminid = '1' GROUP BY b.contractid,a.issuingUnit,a.invoiceTypes

Answer 2

你这另外算就行了，你这需求用这一个sql实现本来就不合理