进销存三表联合查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT a.jiezhuan_prod_code as 商品代码,a.jiezhuan_prod_name as 商品名称, IFNULL(b.jiezhuan_count,0) as 期初数量,IFNULL(b.jiezhuan_money,0) as 期初金额, IFNULL(c.入库数量,0) as 入库数量,IFNULL(c.入库金额,0) as 入库金额, IFNULL(d.出库数量,0) as 出库数量,IFNULL(d.出库金额,0) as 出库金额, IFNULL(b.jiezhuan_count,0)+IFNULL(c.入库数量,0)-IFNULL(d.出库数量,0) as 库存数量, round((IFNULL(b.jiezhuan_money,0)+IFNULL(c.入库金额,0)-IFNULL(d.出库金额,0))/(IFNULL(b.jiezhuan_count,0)+IFNULL(c.入库数量,0)-IFNULL(d.出库数量,0)),2) as 库存单价, IFNULL(b.jiezhuan_money,0)+IFNULL(c.入库金额,0)-IFNULL(d.出库金额,0) as 库存金额 from (select jiezhuan_prod_code,jiezhuan_prod_name from yiyun_kucunjiezhuan UNION select prod_code,prod_name from yiyun_ruku union select prod_codechuku,prod_namechuku from yiyun_chuku ORDER BY  jiezhuan_prod_code) as a left join (SELECT * from yiyun_kucunjiezhuan) as b on a.jiezhuan_prod_code=b.jiezhuan_prod_code left join (SELECT prod_code,sum(ruku_count) as 入库数量,sum(ruku_money) as 入库金额 from yiyun_ruku group by prod_code) as c on a.jiezhuan_prod_code = c.prod_code left join (select prod_codechuku,sum(chuku_count) as 出库数量,sum(chuku_money) as 出库金额 from yiyun_chuku GROUP BY prod_codechuku) as d on a.jiezhuan_prod_code = d.prod_codechuku GROUP BY a.jiezhuan_prod_code ORDER BY 库存金额 desc