三表视图组合，来个大佬
SELECT[设备清单].,[设备清单].[公司],[设备清单].[商品],[清单数量],[单价],[金额],[申请数量],[入库数量] FROM [设备清单]
JOIN (SELECT [公司],[商品],SUM([申请数量]) AS [申请数量] FROM [采购申请] GROUP BY [公司],[商品]) AA
on [设备清单].[公司]=.[公司] AND [设备清单].[商品]=.[商品]
JOIN (SELECT [公司],[商品],SUM([入库数量]) AS [入库数量] FROM [采购入库] GROUP BY [公司],[商品]) BB
on [设备清单].[公司]=.[公司] AND [设备清单].[商品]=.[商品]
这段代码可以合并，但视图设备清单C公司那行数据没出来，来个大佬优化下
悬赏的分给我撒。 没看懂你的清单数量 ，单价，金额在哪张表。没有测试。再改改，大致这样应该可以。
SELECTA.,A.[公司],A.[商品],[清单数量],[单价],[金额],B.[申请数量],C.[入库数量] FROM
（SELECT T1.,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
没看懂你的清单数量 ，单价，金额在哪张表。没有测试。再改改，大致这样应该可以。
SELECTA.,A.[公司],A ...
在设备清单那张表上，大佬~有点看不懂了 军军 发表于 2024-6-13 12:58
没看懂你的清单数量 ，单价，金额在哪张表。没有测试。再改改，大致这样应该可以。
SELECTA.,A.[公司],A ...
SELECTA.[公司],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
SELECTA.[公司],A.[商品],A.[清单数量],A.[单价],A.[金额],B.[申请数量],C.[入库数量] FROM
(SEL ...
这样可以了~谢谢大佬 军军 发表于 2024-6-13 12:58
没看懂你的清单数量 ，单价，金额在哪张表。没有测试。再改改，大致这样应该可以。
SELECTA.,A.[公司],A ...
SELECTA.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
设备清单两表相加结果这个主表也是视图来了~可以解救下吗，大佬，，运行是可以正常，但创视图就报错了 哇哈哈哈 发表于 2024-6-14 02:53
SELECTA.customer_name,A.goods_name,A.code,A.wholesale_number,A.wholesale_price,A.wholesale_amoun ...
已解决，谢谢大佬们 感谢各位大佬的支持~
后边有问题呢欢迎您继续发帖交流:i0tw2:
页:
[1]