(SELECT a.ClassName, a.MasterName, a.SubName, SUM([b.Qty]), SUM([c.Qty])
FROM StockList AS a LEFT JOIN [SELECT ClassName,MasterName,SubName FROM StockList WHERE IfInstock = true] AS b ON a.SubName=b.SubName)
LEFT JOIN [SELECT ClassName, MasterName,SubName FROM stocklist WHERE IfInstock = false] AS c ON a.SubName=c.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName;这样行不行?
FROM StockList AS a LEFT JOIN [SELECT ClassName,MasterName,SubName FROM StockList WHERE IfInstock = true] AS b ON a.SubName=b.SubName)
LEFT JOIN [SELECT ClassName, MasterName,SubName FROM stocklist WHERE IfInstock = false] AS c ON a.SubName=c.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName;这样行不行?
这样不可以,会提示错误:联合查询语法错误
FROM StockList AS a LEFT JOIN [SELECT ClassName,MasterName,SubName FROM StockList WHERE IfInstock = true] AS b ON a.SubName=b.SubName
LEFT JOIN [SELECT ClassName, MasterName,SubName FROM stocklist WHERE IfInstock = false] AS c ON a.SubName=c.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName;这样呢?(去掉括号,好像就对了,呵呵)
StockDate:日期
MasterName:材料名称
SubName:型号
Qty:数量
ClassName :材料类别
IfInstock:是否类型.1表示入库,0表示出库
--------------------------------------------------------------------
RecordID StockDate MasterName SubName Qty ClassName IfInstock
1 02-5-1 合尺 3.5m 2 工具 1
2 02-4-14 合尺 3.5m 1 工具 1
3 02-4-14 镀钛不锈钢板 0.8*4*7 2 板材 1
4 02-4-14 镀钛不锈钢板 0.8*5*6 3 板材 1
5 02-4-14 合尺 3.5m 1 工具 0
6 02-4-14 镀钛不锈钢板 0.8*5*6 2 板材 1
6 02-4-14 镀钛不锈钢板 0.8*5*6 2 板材 0
--------------------------------------------------------------------
查询统计结果:
####################################################################
ClassName MasterName SubName Qty1(入库数量) Qty2(出库数量)
工具 合尺 3.5m 3 1
板材 镀钛不锈钢板 0.8*4*7 2
板材 镀钛不锈钢板 0.8*5*6 5 2
####################################################################
这就是我需要的查询统计结果
谢谢各位专家帮忙
from [select * from StockList where ifinstock=1]. as X
left join [select * from StockList where ifinstock= 0]. as Y
on x.RecordID= y.RecordID group by subname
SELECT x.classname, x.MasterName, x.SubName, sum(x.Qty) AS qty1, sum(y.qty) AS qty2
FROM [select RecordID,classname,mastername,subname,Qty from StockList where ifinstock=true]. AS X LEFT JOIN [select RecordID,classname,mastername,subname,Qty from StockList where ifinstock=false]. AS Y ON x.RecordID= y.RecordID
GROUP BY x.classname, x.mastername, x.subname
您的这种方法只能统计出入库的数量.我在各位的帮助下是这样写的:
SELECT a.ClassName, a.MasterName, a.SubName, SUM([b.Qty]) AS TQ1, SUM([c.Qty]) AS TQ2
FROM (StockList AS a LEFT JOIN [SELECT ClassName,MasterName,SubName,Qty FROM StockList WHERE IfInstock = true]. AS b ON a.SubName=b.SubName) LEFT JOIN [SELECT ClassName, MasterName,SubName,Qty FROM stocklist WHERE IfInstock = false]. AS c ON a.SubName=c.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName;
但是统计出的入库和出库的数量都不正确,不知道我的问题出在什么地方?
from [SELECT ClassName,MasterName,SubName,sum(Qty) as TQ1 FROM StockList WHERE IfInstock = true GROUP BY ClassName,MasterName,SubName]. as a
left join [SELECT ClassName,MasterName,SubName,sum(Qty) as TQ2 FROM StockList WHERE IfInstock = false GROUP BY ClassName,MasterName,SubName]. as b
on a.MasterName=b.MasterName and a.SubName=b.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName
非常感谢您,我这就把分给您.但是有些不明白,既然在第一个虚表中已经使用了GROUP BY统计出了入库数,为什么在外层又会使用GROUP BY a.ClassName, a.MasterName, a.SubName并且结果正确呢?
管理中已经看了给出了分,为什么这个问题还算没有解决的问题呢?
TO xsean() :
如果您发现您的专家分没有曾加,我再去开一个帖子给您100分!
非常感谢您的帮助!