三表视图组合，来个大佬

登录

SELECT [设备清单].[ID],[设备清单].[公司],[设备清单].[商品],[清单数量],[单价],[金额],[申请数量],[入库数量] FROM [设备清单]
JOIN (SELECT [公司],[商品],SUM([申请数量]) AS [申请数量] FROM [采购申请] GROUP BY [公司],[商品]) AA
on [设备清单].[公司]=[AA].[公司] AND [设备清单].[商品]=[AA].[商品]
JOIN (SELECT [公司],[商品],SUM([入库数量]) AS [入库数量] FROM [采购入库] GROUP BY [公司],[商品]) BB
on [设备清单].[公司]=[BB].[公司] AND [设备清单].[商品]=[BB].[商品]

这段代码可以合并，但视图设备清单C公司那行数据没出来，来个大佬优化下

军军 · 军军

悬赏的分给我撒。

军军 · 军军

没看懂你的清单数量，单价，金额在哪张表。没有测试。再改改，大致这样应该可以。
SELECT  A.[ID],A.[公司],A.[商品],[清单数量],[单价],[金额],B.[申请数量],C.[入库数量] FROM
（SELECT T1.[ID],T1.[公司],T1.[商品]
      FROM 设备清单 T1
         GROUP BY T1.[公司],T1.[商品]) A
LEFT JOIN
      (SELECT T2.[公司],T2.[商品],SUM(T2.[申请数量]) AS [申请数量] FROM [采购申请] T2
         GROUP BY T2.[公司],T2.[商品]) B
            on A.[公司]=B.[公司] AND A.[商品]=B.[商品]
LEFT JOIN
   (SELECT T3.[公司],T3.[商品],SUM(T3.[入库数量]) AS [入库数量] FROM [采购入库] T3
         GROUP BY T3.[公司],T3.[商品]) C
         on A.[公司]=C.[公司] AND A.[商品]=C.[商品]

哇哈哈哈 · 哇哈哈哈

军军发表于 2024-6-13 12:58
没看懂你的清单数量，单价，金额在哪张表。没有测试。再改改，大致这样应该可以。
SELECT A.,A.[公司],A ...

在设备清单那张表上，大佬~有点看不懂了

哇哈哈哈 · 哇哈哈哈

军军发表于 2024-6-13 12:58
没看懂你的清单数量，单价，金额在哪张表。没有测试。再改改，大致这样应该可以。
SELECT A.,A.[公司],A ...

SELECT  A.[公司],A.[商品],A.[清单数量],A.[单价],A.[金额],B.[申请数量],C.[入库数量] FROM
(SELECT T1.[公司],T1.[商品],SUM(T1.[清单数量]) AS [清单数量],SUM(T1.[单价]) AS [单价],SUM(T1.[金额]) AS [金额]
      FROM [设备清单] T1
         GROUP BY T1.[公司],T1.[商品]) A
LEFT JOIN
      (SELECT T2.[公司],T2.[商品],SUM(T2.[申请数量]) AS [申请数量] FROM [采购申请] T2
         GROUP BY T2.[公司],T2.[商品]) B
            ON A.[公司]=B.[公司] AND A.[商品]=B.[商品]
LEFT JOIN
   (SELECT T3.[公司],T3.[商品],SUM(T3.[入库数量]) AS [入库数量] FROM [采购入库] T3
         GROUP BY T3.[公司],T3.[商品]) C
            ON A.[公司]=C.[公司] AND A.[商品]=C.[商品]

哇哈哈哈 · 哇哈哈哈

哇哈哈哈发表于 2024-6-13 14:42
SELECT A.[公司],A.[商品],A.[清单数量],A.[单价],A.[金额],B.[申请数量],C.[入库数量] FROM
(SEL ...

这样可以了~谢谢大佬

哇哈哈哈 · 哇哈哈哈

军军发表于 2024-6-13 12:58
没看懂你的清单数量，单价，金额在哪张表。没有测试。再改改，大致这样应该可以。
SELECT A.,A.[公司],A ...

SELECT  A.customer_name,A.goods_name,A.code,A.wholesale_number,A.wholesale_price,A.wholesale_amount,B.purchase_quantity,C.purchase_quantity1,D.purchase_quantity2 FROM
(SELECT T1.customer_name,T1.goods_name,T1.code,SUM(T1.wholesale_number) AS wholesale_number,SUM(T1.wholesale_price) AS wholesale_price,SUM(T1.wholesale_amount) AS wholesale_amount
      FROM 设备清单两表相加结果 T1
         GROUP BY T1.customer_name,T1.goods_name,T1.code) A
LEFT JOIN
      (SELECT T2.customer_name,T2.goods_name,T2.code,SUM(T2.purchase_quantity) AS purchase_quantity FROM tb_purchase_request_hua T2
         GROUP BY T2.customer_name,T2.goods_name,T2.code) B
            ON A.customer_name=B.customer_name AND A.goods_name=B.goods_name AND A.code=B.code
LEFT JOIN
   (SELECT T3.customer_name,T3.goods_name,T3.code,SUM(T3.purchase_quantity1) AS purchase_quantity1 FROM tb_purchase_receipt_hua T3
         GROUP BY T3.customer_name,T3.goods_name,T3.code) C
            ON A.customer_name=C.customer_name AND A.goods_name=C.goods_name AND A.code=C.code
LEFT JOIN
   (SELECT T4.customer_name,T4.goods_name,T4.code,SUM(T4.purchase_quantity2) AS purchase_quantity2 FROM tb_installation_checklist_hua T4
         GROUP BY T4.customer_name,T4.goods_name,T4.code) D
            ON A.customer_name=D.customer_name AND A.goods_name=D.goods_name AND A.code=D.code

报错View's SELECT contains a subquery in the FROM clause
设备清单两表相加结果  这个主表也是视图来了~可以解救下吗，大佬，，运行是可以正常，但创视图就报错了