修改一下BOM递归!

Question

本贴与该帖子很类型，请参考14楼答案：http://topic.csdn.net/u/20111129/14/1b3c1014-25eb-42c2-b293-e78cdaac737c.html，希望各位大侠在此基础上修改。
有表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(半成品或物料)        BOM_UNIT         QTY
99-88551-01030           97-88551-01030                   PCS           0.1        ----此为最高层
97-88551-01030           96-88551-01010                   PCS           0.1
96-88551-01010           #8003042-01010                   G             0.1
96-88551-01010           #6300035-01010                   G             0.1
96-88551-01010           20-88551-01030                   PCS           0.1
20-88551-01030           21-00001-00010                   G             0.1
20-88551-01030           22-00424-01010                   G             0.198485-00001              97485-00001                      PCS           0.2        ----此为最高层
97485-00001              90485-00001                      PCS           0.2
90485-00001              #8000-01010                      G             0.2
90485-00001              20485-00001                      PCS           0.2
20485-00001              21485-00001                      PCS           0.2
20485-00001              22485-00001                      G             0.298-78496-SP417K          #98-78496-SP417K                 G             0.3         ----此为最高层
98-78496-SP417K          97-78496-SP417K                  PCS           0.3
97-78496-SP417K          96-78496-SP2027                  PCS           0.3
97-78496-SP417K          #7678496-00010                   G             0.3
97-78496-SP417K          #88-78496-SP2027                 G             0.3
96-78496-SP2027          94-78496-SP2027                  PCS           0.3
96-78496-SP2027          #87-78496-SP2027                 G             0.3
94-78496-SP2027          90-78496-01010                   PCS           0.3
90-78496-01010           87-02029-00020                   G             0.3
90-78496-01010           02-00002-01011                   G             0.3
......我想要的结果：当输入一个tb1表里面的任何一个成品,比如99-88551-01030的时候，就查找BOM1表里所有的ITEM_CODE列的数据，（除了最高层的那个成品不显示），我要的结果如下：ITEM_CODE                     BOM_UNIT         QYT 
97-88551-01030                   PCS           0.1      
96-88551-01010                   PCS           0.1
#8003042-01010                   G             0.1
#6300035-01010                   G             0.1
20-88551-01030                   PCS           0.1
21-00001-00010                   G             0.1
22-00424-01010                   G             0.1
数据库环境：sql 2000

Answer 1

测试数据：create table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),BOM_UNIT varchar(50),QTY decimal(18,2))insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('99-88551-01030','97-88551-01030','PCS','0.1')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('97-88551-01030','96-88551-01010','PCS','0.1')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('96-88551-01010','#8003042-01010','G','0.1')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('96-88551-01010','#6300035-01010','G','0.1')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('96-88551-01010','20-88551-01030','PCS','0.1')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('20-88551-01030','21-00001-00010','G','0.1')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('20-88551-01030','22-00424-01010','G','0.1')insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('98-78496-SP417K','#98-78496-SP417K','G','0.3')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('98-78496-SP417K','97-78496-SP417K','PCS','0.3')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('97-78496-SP417K','96-78496-SP2027','PCS','0.3')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('97-78496-SP417K','#7678496-00010','G','0.3')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('97-78496-SP417K','#88-78496-SP2027','G','0.3')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('96-78496-SP2027','94-78496-SP2027','PCS','0.3')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('96-78496-SP2027','#87-78496-SP2027','G','0.3')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('94-78496-SP2027','90-78496-01010','PCS','0.3')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('90-78496-01010','87-02029-00020','G','0.3')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('90-78496-01010','02-00002-01011','G','0.3')insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('98485-00001','97485-00001','PCS','0.2')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('97485-00001','90485-00001','PCS','0.2')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('90485-00001','#8000-01010','PCS','0.2')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('90485-00001','20485-00001','PCS','0.2')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('20485-00001','21485-00001','G','0.2')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT,QTY) 
values('20485-00001','22485-00001','G','0.2')
GO

Answer 2

create table #(ITEM_CODE varchar(50),BOM_UNIT varchar(50),QTY decimal(18,2), Lv int)declare @Lv int = 1
insert # select ITEM_CODE, BOM_UNIT, QTY, @Lv from BOM1 where PARENT='99-88551-01030'
while @@rowcount>0
begin
set @Lv=@Lv+1
insert # select a.ITEM_CODE, a.BOM_UNIT, a.QTY, @Lv from BOM1 a, # b where b.Lv=@Lv-1 and a.PARENT=b.ITEM_CODE
endselect * from #/*
ITEM_CODE                                          BOM_UNIT                                           QTY                                     Lv
-------------------------------------------------- -------------------------------------------------- --------------------------------------- -----------
97-88551-01030                                     PCS                                                0.10                                    1
96-88551-01010                                     PCS                                                0.10                                    2
#8003042-01010                                     G                                                  0.10                                    3
#6300035-01010                                     G                                                  0.10                                    3
20-88551-01030                                     PCS                                                0.10                                    3
21-00001-00010                                     G                                                  0.10                                    4
22-00424-01010                                     G                                                  0.10                                    4
*/

Answer 3

更正细节
--declare @Lv int = 1 -- 2000 无此语法
declare @Lv int
set @Lv = 1

Answer 4

COPY 你的代码运行有错：消息 156，级别 15，状态 1，第 4 行
在关键字 'insert' 附近有语法错误。
消息 137，级别 15，状态 2，第 4 行
必须声明变量 '@Lv'。

Answer 5

我刚小测试了，可以。还有个疑问，那每查询一次都要创建临时表了，实际项目中BOM1表有几十万的数据，速度方面有没有影响呢？

Answer 6

影响不大，这是2000树递归唯一的方法，BOM1.PARENT索引应该不会慢，临时表数据不多的，每次Lv筛选更少了。

Answer 7

测试了几个PARENT没问题，结贴。