我如何在sql上加起来，使它不会't因为分组依据而加倍

Question

当我运行程序时，数字的结果会使一个数字合并，并使数字变成多个结果。

SELECT pengiriman_supply.po_nomor
, data_supplier.nama_supplier
, data_barang.nama_barang,
((sum(pengiriman_supply.jumlah))-(ifnull(masuk.terima,0))) as total 
FROM pengiriman_supply INNER JOIN data_supplier ON pengiriman_supply.idsupplier = data_supplier.id_supplier 
INNER JOIN data_barang ON pengiriman_supply.idbarang = data_barang.idbarang 
left JOIN masuk on masuk.refrence = pengiriman_supply.po_nomor 
where pengiriman_supply.tanggal between date_sub(curdate(), interval 120 day) and curdate() 
group by pengiriman_supply.po_nomor
ORDER BY pengiriman_supply.po_nomor desc;

结果是在此处输入图像描述

Answer 1

似乎您正试图获得每个po number的sum()。让我们把它放在一个subquery，然后再放在join中——那些你想要信息显示的表。

select t.po_nomor
, p.nama_supplier
, p1.nama_barangfrom
, t.total
from 
(select t1.po_nomor         
, (sum(t1.jumlah)-coalesce(t4.terima,0)) as total 
from pengiriman_supply t1
inner join data_supplier t2 on t1.idsupplier = t2.id_supplier 
inner join data_barang t3 on t1.idbarang = t3.idbarang 
left join masuk t4 on t4.refrence = t1.po_nomor 
where t1.tanggal between date_sub(curdate(), interval 120 day) and curdate() 
group by t1.po_nomor) t
inner join pengiriman_supply s on s.po_nomor = t.po_nomor
inner join data_supplier p on s.idsupplier = p.id_supplier 
inner join data_barang p1 on t.idbarang = p1.idbarang 
order by t.po_nomor desc;