本帖最后由 伟伟 于 2020-8-26 09:17 编辑
求助大神结改一下视图:多个表拼接按ID求和错误翻倍
SELECT
A1.ID as 物料清单子表ID,
A1.数量 as 需求数量,
IFNULL(SUM(A2.数量),0) as 采购数量,
IFNULL(SUM(A3.计划到货数量),0) as 计划到货数量,
IFNULL(SUM(A4.到货数量),0) as 已到货数量,
IFNULL(SUM(A5.不合格数量),0) as 不合格数量,
A1.数量-IFNULL(SUM(A4.到货数量),0)+IFNULL(SUM(A5.不合格数量),0) as 还差数量
FROM 物料清单子表 as A1
LEFT JOIN 采购订单子表 as A2 ON A1.ID = A2.物料清单子表ID
LEFT JOIN 计划到货通知子表 as A3 ON A1.ID = A3.物料清单子表ID
LEFT JOIN 到货报检子表 as A4 ON A1.ID = A4.物料清单子表ID
LEFT JOIN 检验报告子表 as A5 ON A1.ID = A5.物料清单子表ID
GROUP BY
A1.ID
|