数据拆分
借用别人的部分数据
表A1:
item order_qty mdate
A 850 07-18-09
A 300 07-20-09
B 150 07-18-09
表A2:
ID item bz_qty mdate
1 A 300 07-19-09
2 A 1000 07-19-09
1 B 200 07-21-09
如何通过查询或代码得到表 A3: <如下>
ID item order_qty kb_qty kb
1 A 1150 300 OK
2 A 850 1000 -150
1 B 150 200 -50
[解决办法]
select ID,item,dsum('order_qty','A1','item="' & item & '"')-dsum('bz_qty','A2','item="' & item & '" and ID<' & ID),bz_qty ,
IIF(dsum('order_qty','A1','item="' & item & '"')-dsum('bz_qty','A2','item="' & item & '" and ID<' & ID)>bz_qty,
'OK',
dsum('order_qty','A1','item="' & item & '"')-dsum('bz_qty','A2','item="' & item & '" and ID<' & ID)-bz_qty)
from A2
order by item,id
[解决办法]
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
