重问数据拆分
上次在http://topic.csdn.net/u/20100303/16/ab7e7c23-26e7-45ec-b2e2-a364ea6912b7.html
问过一次
但发现不按照SELECT A.ID, A.item, A.bz_qty, A.mdate, max(c.qq1)-NZ(Sum(B.bz_qty),0) AS QQ,
iif(max(c.qq1)-NZ(Sum(B.bz_qty),0) -(a.bz_qty)>0,'ok',max(c.qq1)-NZ(Sum(B.bz_qty),0) -(a.bz_qty)) AS QQ1
FROM (TT1 AS A LEFT JOIN TT1 AS B ON A.[item] = B.[item] AND A.ID>B.ID)
LEFT JOIN (SELECT A1.item, Sum(A1.[order_qty]) AS QQ1
FROM A1
GROUP BY A1.item
) c on A.[item] = c.[item]
GROUP BY A.ID, A.item, A.bz_qty, A.mdate
order by
A.ID, A.item
排布则会提示出现from语句错误
出现空格改变后在还原至以前的排布也一样提示from语句错误
不知道是怎么回事
有表A1:
型号物料交货数量
LH020A50
LH031C250
LH040C500
LH020B50
LH020A50
表A2:
编号批号型号物料计划量
1JC02LH020A100
1JC02LH020B100
2JC03LH031C300
3JC04LH040C400
4JC05LH020A50
4JC05LH020B50
5JC09LH040C100
想得到表:
编号批号型号物料交货总数领出总数欠料数
1JC02LH020A100100OK
1JC02LH020B50100-50
2JC03LH031C250300-50
3JC04LH040C500400OK
4JC05LH020A050-50
4JC05LH020B-5050-100
5JC09LH040C100100OK
[解决办法]
SELECT A.ID, A.item, A.bz_qty, A.mdate, max(c.qq1)-NZ(Sum(B.bz_qty),0) AS QQ,
iif(max(c.qq1)-NZ(Sum(B.bz_qty),0) -(a.bz_qty)>0,'ok',max(c.qq1)-NZ(Sum(B.bz_qty),0) -(a.bz_qty)) AS QQ1
FROM (TT1 AS A LEFT JOIN TT1 AS B ON A.[item] = B.[item] AND A.ID>B.ID)
LEFT JOIN
(SELECT A1.item, Sum(A1.[order_qty]) AS QQ1 FROM A1 GROUP BY A1.item ) c on A.[item] = c.[item]
GROUP BY A.ID, A.item, A.bz_qty, A.mdate
order by A.ID, A.item
没有问题
