修改一下BOM递归!很类似的2个帖子，有测试数据，在线等！

wangxiaofeiwuqiao 2012-05-08 09:17:46

可参考原帖：http://topic.csdn.net/u/20111129/14/1b3c1014-25eb-42c2-b293-e78cdaac737c.html，原帖采用12楼方法。
有表tb1:(ITEM_CODE指成品)



ITEM_CODE

99-88551-01030

#98-78496-SP417K

......

有表BOM1：
其中PARENT与ITEM_CODE可以存在不定的多层的递归关系，层层递归，99下面是98，98下是97,97下是94,94下是91，91下面是20或者21,22等等。例如：



PARENT(成品)            ITEM_CODE(半成品或物料)      SHW_QTY(用量)    BOM_UNIT（单位）

99-88551-01030           97-88551-01030               2                 PCS      ----此为最高层

97-88551-01030           96-88551-01010               1                 PCS

96-88551-01010           #8003042-01010               1.5               G

96-88551-01010           #6300035-01010               3                 G

96-88551-01010           20-88551-01030               2.2               PCS

96-88551-01010           90-88551-01030               2.2               PCS

90-88551-01030           21-00001-00010               1.5               G

90-88551-01030           22-00424-01010               2.5               G

20-88551-01030           21-00001-00010               4.5               G

20-88551-01030           22-00424-01010               2                 G



98485-00001              97485-00001                  5                 PCS     ----此为最高层

97485-00001              90485-00001                  4                 PCS

90485-00001              #8000-01010                  2                 G

90485-00001              20485-00001                  1.5               PCS

20485-00001              21485-00001                  2                 PCS

20485-00001              22485-00001                  3                 G



#98-78496-SP417K         97-78496-SP417K              5.2              PCS     ----此为最高层

97-78496-SP417K          96-78496-SP2027              6                 PCS

97-78496-SP417K          #7678496-00010               5                 G

97-78496-SP417K          #88-78496-SP2027             4                 G

96-78496-SP2027          94-78496-SP2027              1                 PCS

96-78496-SP2027          #87-78496-SP2027             6                 G

94-78496-SP2027          90-78496-01010               2.2               PCS

90-78496-01010           87-02029-00020               3.5               G

90-78496-01010           02-00002-01011               1                 G

我想要的结果：通过查找成品表tb1中的所有成品，找出它们下面的所有物料和半成品，包含显示用量和单位.
注意的是：成品99-88551-01030下面的90-88551-01030和20-88551-01030，他们的底层物料都一样，但是用量不用，都要显示。



ITEM_CODE                                 PARENT                    SHW_QTY             BOM_UNIT

97-88551-01030                          99-88551-01030                2                  PCS

96-88551-01010                          99-88551-01030                1                  PCS

#8003042-01010                          99-88551-01030                1.5                G

#6300035-01010                          99-88551-01030                3                  G

20-88551-01030                          99-88551-01030                2.2                PCS

90-88551-01030                          99-88551-01030                2.2                PCS

21-00001-00010                          99-88551-01030                1.5                G

22-00424-01010                          99-88551-01030                2.5                G

21-00001-00010                          99-88551-01030                4.5                G

22-00424-01010                          99-88551-01030                2                  G



97-78496-SP417K                         98-78496-SP417K               5.2                PCS

96-78496-SP2027                         98-78496-SP417K               6                  PCS

#7678496-00010                          98-78496-SP417K               5                  G

#88-78496-SP2027                        98-78496-SP417K               4                  G

94-78496-SP2027                         98-78496-SP417K               1                  PCS

#87-78496-SP2027                        98-78496-SP417K               6                  G

90-78496-01010                          98-78496-SP417K               2.2                PCS

87-02029-00020                          98-78496-SP417K               3.5                G

02-00002-01011                          98-78496-SP417K               1                  G

数据库环境： SQL 2000
提示：直接在原12楼的答案上加上BOM1表的另外2个字段SHW_QTY,BOM_UNIT好像不行。

...全文

244 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

wangxiaofeiwuqiao 2012-05-08

打赏
举报

[Quote=引用 6 楼的回复:]
SQL code

create table BOM1(PARENT varchar(50),
ITEM_CODE varchar(50),SHW_QTY FLOAT,BOM_UNIT varchar(50))

insert into BOM1(PARENT,ITEM_CODE,SHW_QTY,BOM_UNIT)
SELECT '99-88551-01030','97-88551-……
[/Quote]

方法不错，测试通过，结贴。

唐诗三百首 2012-05-08

打赏
举报



create table BOM1(PARENT varchar(50),

ITEM_CODE varchar(50),SHW_QTY FLOAT,BOM_UNIT varchar(50))



insert into BOM1(PARENT,ITEM_CODE,SHW_QTY,BOM_UNIT) 

SELECT '99-88551-01030','97-88551-01030','2','PCS' UNION ALL

SELECT '97-88551-01030','96-88551-01010','1','PCS' UNION ALL

SELECT '96-88551-01010','#8003042-01010','1.5','G' UNION ALL

SELECT '96-88551-01010','#6300035-01010','3','G' UNION ALL

SELECT '96-88551-01010','20-88551-01030','2.2','PCS' UNION ALL

SELECT '96-88551-01010','90-88551-01030','2.2','PCS' UNION ALL

SELECT '90-88551-01030','21-00001-00010','1.5','G' UNION ALL

SELECT '90-88551-01030','22-00424-01010','2.5','G' UNION ALL

SELECT '20-88551-01030','21-00001-00010','4.5','G' UNION ALL

SELECT '20-88551-01030','22-00424-01010','2','G' UNION ALL



SELECT '#98-78496-SP417K','97-78496-SP417K','5.2','PCS' UNION ALL

SELECT '97-78496-SP417K','96-78496-SP2027','6','PCS' UNION ALL

SELECT '97-78496-SP417K','#7678496-00010','5','G' UNION ALL

SELECT '97-78496-SP417K','#88-78496-SP2027','4','G' UNION ALL

SELECT '96-78496-SP2027','94-78496-SP2027','1','PCS' UNION ALL

SELECT '96-78496-SP2027','#87-78496-SP2027','6','G' UNION ALL

SELECT '94-78496-SP2027','90-78496-01010','2.2','PCS' UNION ALL

SELECT '90-78496-01010','87-02029-00020','3.5','G' UNION ALL

SELECT '90-78496-01010','02-00002-01011','1','G' UNION ALL



SELECT '98485-00001','97485-00001','5','PCS' UNION ALL

SELECT '97485-00001','90485-00001','4','PCS' UNION ALL

SELECT '90485-00001','#8000-01010','2','PCS' UNION ALL 

SELECT '90485-00001','20485-00001','1.5','PCS' UNION ALL

SELECT '20485-00001','21485-00001','2','G' UNION ALL

SELECT '20485-00001','22485-00001','3','G' 



create table tb1(ITEM_CODE varchar(50))



insert into tb1 values('99-88551-01030')

insert into tb1 values('#98-78496-SP417K')





DECLARE @lev INT

SET @lev=0



SELECT t.*,lev=0,t.PARENT AS PG 

INTO #B 

FROM BOM1 t join tb1 e on t.PARENT = e.ITEM_CODE



WHILE @@rowcount>0

BEGIN

SET @lev=@lev+1

INSERT INTO #B 

SELECT a.*,lev=b.lev+1,b.PG

FROM BOM1 AS a 

INNER JOIN #B AS b 

on b.ITEM_CODE=a.PARENT AND b.lev=@lev-1

END





select ITEM_CODE,PARENT,SHW_QTY,BOM_UNIT

from

(SELECT ITEM_CODE,PG 'PARENT',SHW_QTY,BOM_UNIT,lev 

 FROM #B) t order by t.PARENT desc,t.lev





ITEM_CODE           PARENT              SHW_QTY BOM_UNIT

------------------- ------------------- ------- --------

97-88551-01030      99-88551-01030      2       PCS

96-88551-01010      99-88551-01030      1       PCS

#8003042-01010      99-88551-01030      1.5     G

#6300035-01010      99-88551-01030      3       G

20-88551-01030      99-88551-01030      2.2     PCS

90-88551-01030      99-88551-01030      2.2     PCS

21-00001-00010      99-88551-01030      1.5     G

22-00424-01010      99-88551-01030      2.5     G

21-00001-00010      99-88551-01030      4.5     G

22-00424-01010      99-88551-01030      2       G

97-78496-SP417K     #98-78496-SP417K    5.2     PCS

96-78496-SP2027     #98-78496-SP417K    6       PCS

#7678496-00010      #98-78496-SP417K    5       G

#88-78496-SP2027    #98-78496-SP417K    4       G

94-78496-SP2027     #98-78496-SP417K    1       PCS

#87-78496-SP2027    #98-78496-SP417K    6       G

90-78496-01010      #98-78496-SP417K    2.2     PCS

87-02029-00020      #98-78496-SP417K    3.5     G

02-00002-01011      #98-78496-SP417K    1       G



(19 row(s) affected)

Mr_Nice 2012-05-08

打赏
举报

把数量相关信息，加上即可了。一样的算法。 LZ自己调试一下即可。

wangxiaofeiwuqiao 2012-05-08

打赏
举报

[Quote=引用 3 楼的回复:]
既然两个帖子很类似，为什么不自己写呢？
[/Quote]
我弄了好久，弄不出来。

让你望见影子的墙 2012-05-08

打赏
举报

既然两个帖子很类似，为什么不自己写呢？

wangxiaofeiwuqiao 2012-05-08

打赏
举报

成品99-88551-01030下面的90-88551-01030和20-88551-01030，他们的底层物料都一样，但是用量不同（用量不同），都要显示。

wangxiaofeiwuqiao 2012-05-08

打赏
举报

测试数据：



create table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),SHW_QTY FLOAT,BOM_UNIT varchar(50))

insert into BOM1(PARENT,ITEM_CODE,SHW_QTY,BOM_UNIT) 

SELECT '99-88551-01030','97-88551-01030','2','PCS' UNION ALL

SELECT '97-88551-01030','96-88551-01010','1','PCS' UNION ALL

SELECT '96-88551-01010','#8003042-01010','1.5','G' UNION ALL

SELECT '96-88551-01010','#6300035-01010','3','G' UNION ALL

SELECT '96-88551-01010','20-88551-01030','2.2','PCS' UNION ALL

SELECT '96-88551-01010','90-88551-01030','2.2','PCS' UNION ALL

SELECT '90-88551-01030','21-00001-00010','1.5','G' UNION ALL

SELECT '90-88551-01030','22-00424-01010','2.5','G' UNION ALL

SELECT '20-88551-01030','21-00001-00010','4.5','G' UNION ALL

SELECT '20-88551-01030','22-00424-01010','2','G' UNION ALL



SELECT '#98-78496-SP417K','97-78496-SP417K','5.2','PCS' UNION ALL

SELECT '97-78496-SP417K','96-78496-SP2027','6','PCS' UNION ALL

SELECT '97-78496-SP417K','#7678496-00010','5','G' UNION ALL

SELECT '97-78496-SP417K','#88-78496-SP2027','4','G' UNION ALL

SELECT '96-78496-SP2027','94-78496-SP2027','1','PCS' UNION ALL

SELECT '96-78496-SP2027','#87-78496-SP2027','6','G' UNION ALL

SELECT '94-78496-SP2027','90-78496-01010','2.2','PCS' UNION ALL

SELECT '90-78496-01010','87-02029-00020','3.5','G' UNION ALL

SELECT '90-78496-01010','02-00002-01011','1','G' UNION ALL



SELECT '98485-00001','97485-00001','5','PCS' UNION ALL

SELECT '97485-00001','90485-00001','4','PCS' UNION ALL

SELECT '90485-00001','#8000-01010','2','PCS' UNION ALL 

SELECT '90485-00001','20485-00001','1.5','PCS' UNION ALL

SELECT '20485-00001','21485-00001','2','G' UNION ALL

SELECT '20485-00001','22485-00001','3','G' 



create table tb1(ITEM_CODE varchar(50))

insert into tb1 values('99-88551-01030')

insert into tb1 values('#98-78496-SP417K')