进销存三表联合查询

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