SELECT ReTable.Lvl,
max(#ReTable.item) as Item,
max(INVMC.MC007) as Mc007,
sum(#aa.zt) sum_zt,
max(#ReTable.MB004) as Mb004,
max(INVMC.MC002) as mc002,
max(#ReTable.MB002)as mb002,
max(#ReTable.MB003) as mb003
FROM #ReTable,INVMC,PURTD,#aa
where INVMC.MC001=#ReTable.item and #ReTable.item=#aa.item
group by ReTable.item,INVMC.MC007,#ReTable.MB004,INVMC.MC002,#ReTable.MB002,#ReTable.MB00
max(#ReTable.item) as Item,
max(INVMC.MC007) as Mc007,
sum(#aa.zt) sum_zt,
max(#ReTable.MB004) as Mb004,
max(INVMC.MC002) as mc002,
max(#ReTable.MB002)as mb002,
max(#ReTable.MB003) as mb003
FROM #ReTable,INVMC,PURTD,#aa
where INVMC.MC001=#ReTable.item and #ReTable.item=#aa.item
group by ReTable.item,INVMC.MC007,#ReTable.MB004,INVMC.MC002,#ReTable.MB002,#ReTable.MB00
#ReTable.item ,
INVMC.MC007,
sum(#aa.zt) sum_zt,
#ReTable.MB004 ,
INVMC.MC002 ,
#ReTable.MB002 2,
#ReTable.MB003
FROM #ReTable,INVMC,PURTD,#aa
where INVMC.MC001=#ReTable.item and #ReTable.item=#aa.item
group by ReTable.item,INVMC.MC007,#ReTable.MB004,INVMC.MC002,#ReTable.MB002,#ReTable.MB00
select item,sum(zt) from #aa
group by item
--是这个意思吗?
如果要将#aa表格中的zt字段中相加时
前提条件是item字段下的数据相同时
才允许将zt字段下的数据相加
group by item
select item,sum(zt) from #aa
group by item
--这个就是那个意思啊,item相同的汇总zt啊?
#ReTable.item,
max(INVMC.MC007) as Mc007,
sum(#aa.zt) sum_zt,
max(#ReTable.MB004) as Mb004,
max(INVMC.MC002) as mc002,
max(#ReTable.MB002)as mb002,
max(#ReTable.MB003) as mb003
FROM #ReTable,INVMC,PURTD,#aa
where INVMC.MC001=#ReTable.item and #ReTable.item=#aa.item
group by ReTable.Lvl,ReTable.item
#ReTable.item,
max(INVMC.MC007) as Mc007,
sum(#aa.zt) sum_zt,
max(#ReTable.MB004) as Mb004,
max(INVMC.MC002) as mc002,
max(#ReTable.MB002)as mb002,
max(#ReTable.MB003) as mb003
FROM #ReTable,INVMC,PURTD,#aa
where INVMC.MC001=#ReTable.item and #ReTable.item=#aa.item
group by ReTable.item
本来只有几千的数量
但按照你们的语句写完之后运行出来的zt数据非常大
select item,sum(zt) from #aa
group by item
--先对#aa进行上面的出来放到另一个临时表,
--再和其他关联!!
--关联也就用了#aa的item和zt字段!
select item,sum(zt) zt into #aaa
from #aa
group by item
你有没有MSN及其它联系方法MSN是[email protected]
insert #aa(item,zt)
select TD004,TD008-TD015
from PURTD,#ReTable
where TD016='N'
and TD018='Y'
and TD004=#ReTable.itemselect item,sum(zt) zt into #aaa
from #aa
group by itemSELECT #ReTable.Lvl,#ReTable.item,INVMC.MC007,
#aaa.zt,#ReTable.MB004,INVMC.MC002,
#ReTable.MB002,#ReTable.MB003
FROM #ReTable,INVMC,PURTD,#aaa
where INVMC.MC001=#ReTable.item
and #ReTable.item=#aaa.item
我已经按照你的方法做了
在途数量是可以正确的显示出来
呵呵
但是每一条数据重复的显示上百上千次
这是问题哦??
--加个distinct或重新修改你的语句逻辑,
--不知道你的表结构及之间的关系!
SELECT #ReTable.Lvl,#ReTable.item,INVMC.MC007,
#aa.zt,#ReTable.MB004,INVMC.MC002,
#ReTable.MB002,#ReTable.MB003
FROM #ReTable,INVMC,PURTD,#aa
where INVMC.MC001=#ReTable.item
and #ReTable.item=#aa.item
--自己看看吧!难怪会有你以下说的问题--zt的数据有问题
--本来只有几千的数量
--但按照你们的语句写完之后运行出来的zt数据非常大
如下
SELECT max(#ReTable.Lvl)as Lvl,max(#ReTable.item)as item,max(INVMC.MC007)as MC007,max(#aaa.zt)as zt,max(#ReTable.MB004)asMB004,max(INVMC.MC002)as MC002,max(#ReTable.MB002)as MB002,max(#ReTable.MB003)as MB003 FROM #ReTable,INVMC,PURTD,#aaa where INVMC.MC001=#ReTable.item and #ReTable.item=#aaa.item
但最后输出的数据却只有一行,即一个数据,但实际是有很多行数据的.
所写这些语句是想带出ERP系统只的采购在途数量.
不知道你对ERP系统及ERP系统中的BOM(BOM是由很多原材料的品号组成)有没有了解,结构类似如下:
1
|-------|
2 4
|-----| |-----|
3 4 5 4先展BOM后.
再用BOM显示出来的每个品号调出INVMC中库存单身档的MC007字段的库存数量.
又用BOM显示出来的每个品号调出PURTD中采购单身档的TD008字段减去TD015字段的数据.
最后得到采购在途数量.
--不了解,那就这样:
SELECT distinct #ReTable.Lvl,#ReTable.item,INVMC.MC007,
#aaa.zt,#ReTable.MB004,INVMC.MC002,
#ReTable.MB002,#ReTable.MB003
FROM #ReTable,INVMC,PURTD,#aaa
where INVMC.MC001=#ReTable.item
and #ReTable.item=#aaa.item