SELECT m.branchid, m.prodid, m.lotno, m.customerid, m.INBOUNDQTY, m.billid, m.billingdate, j.costprice, j.costaccounting, k.storeinventory, j.costprice * k.storeinventory as storeamount, c.custno, c.custname, d.prodno, d.prodname, d.packageunit, d.manufacture, d.prodspecification, d. purchaser, d.busitypetext, r.pk, r.NOTICESUMID, r.NOTICEDETID, d.PURCHASECHANNAME from tb_gos_account_o_prodaccs m inner join (select t.branchid, t.PRODID, t.lotno, max(PK) as pk from tb_gos_account_o_prodaccs t where substr(billid, 4, 3) = 'JHA' GROUP BY branchid, PRODID, lotno) n on m.branchid = n.branchid and m.pk = n.pk inner join (select branchid, prodid, lotno, sum(invbalqty) as storeinventory from SX_KH_lotinven where branchid = 'FWX' and to_char(createtime, 'yyyy-mm-dd') = '2019-03-01' group by branchid, prodid, lotno) k on m.prodid = k.prodid and m.lotno = k.lotno and m.branchid = k.branchid inner join tb_gos_account_o_braninven j on k.prodid = j.prodid and k.branchid = j.branchid left join vw_common_cust c on m.customerid = c.custid and m.branchid = c.branchid left join vw_common_prod d on m.prodid = d.prodid and m.branchid = d.branchid LEFT JOIN tb_gos_purchase_purstockinDET r on m.billid = r.billid and m.prodid = r.proid and m.lotno = r.lotno where k.storeinventory != 0 and m.branchid = 'AWX'PLSQL里可以查到数据
