如何实现物料清单的循环查找合并？

woboy 发布于 2010-08-22 23:55， 768 次点击

父件           子件          定额      工艺链
11HQ653        211HQ653-01    1       11HQ653/211HQ653-01
211HQ653-01    211HA653-01    1       11HQ653/211HQ653-01/211HA653-01
211HA653-01    211A653-01     1       11HQ653/211HQ653-01/211HA653-01/211A653-01
211A653-01     211G653-01     1       11HQ653/211HQ653-01/211HA653-01/211A653-01/211G653-01
211G653-01     211Y653-01     1       11HQ653/211HQ653-01/211HA653-01/211A653-01/211G653-01/211Y653-01
211Y653-01     401990       0.012     11HQ653/211HQ653-01/211HA653-01/211A653-01/211G653-01/211Y653-01/401990
11HQ653        399Y81196       1      11HQ653/399Y81196
11HQ653        5016547       0.01     11HQ653/5016547
11HQ653        503M6547        1      11HQ653/503M6547
11HQ653        5046547        0.02    11HQ653/5046547
11HQ653        5070060         1      11HQ653/5070060

通过查询父件和子件合并出“工艺链”列的内容，通过SQL该如何做才可以实现？
请各位大师指点指点！谢谢！

8 回复

aei1352010-08-23 17:22

declare @CList table(parentid varchar(50),sonid varchar(50),[desc] varchar(500))
declare @tmpCList table(parentid varchar(50),sonid varchar(50))
declare @parentid varchar(50),@sonid varchar(20),@tmpfacther varchar(50),@tmpsonid varchar(50)
insert into @clist(parentid,sonid) select parentid,sonid from ceshi
while exists(select 0  from @CList where [desc] is null)
begin
    select top 1 @parentid=parentid,@sonid=sonid from @CList where [desc] is null
    update @CList set [desc]=@parentid+'/'+sonid where parentid=@parentid and sonid=@sonid
    set @tmpfacther=@parentid
    insert into @tmpCList select parentid,sonid from ceshi
    while exists(select 0 from @tmpCList where sonid=@tmpfacther)
    Begin
        select top 1 @tmpsonid=sonid,@tmpfacther=parentid from @tmpCList where sonid=@tmpfacther
        update @CList set [desc]=@tmpfacther+'/'+[desc] where parentid=@parentid and sonid=@sonid
    End
end
select * from @CList

woboy2010-08-23 17:36

尊敬的版主：
谢谢你的帮助！但我实在看不明白