当前位置：首页 → 问答吧 → 简单的递归算法

简单的递归算法

时间：2011-11-11

来源：互联网

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


PARENT(成品)            ITEM_CODE(半成品或物料)        BOM_UNIT
99-88551-01030           97-88551-01030                   PCS  ----此为最高层
97-88551-01030           96-88551-01010                   PCS
96-88551-01010           #8003042-01010                   G
96-88551-01010           #6300035-01010                   G
96-88551-01010           20-88551-01030                   PCS
20-88551-01030           21-00001-00010                   G
20-88551-01030           22-00424-01010                   G

98485-00001              97485-00001                      PCS  ----此为最高层
97485-00001              90485-00001                      PCS
90485-00001              #8000-01010                      G
90485-00001              20485-00001                      PCS
20485-00001              21485-00001                      PCS
20485-00001              22485-00001                      G

98-78496-SP417K          #98-78496-SP417K                 G       ----此为最高层
98-78496-SP417K          97-78496-SP417K                  PCS
97-78496-SP417K          96-78496-SP2027                  PCS
97-78496-SP417K          #7678496-00010                   G
97-78496-SP417K          #88-78496-SP2027                 G
96-78496-SP2027          94-78496-SP2027                  PCS
96-78496-SP2027          #87-78496-SP2027                 G
94-78496-SP2027          90-78496-01010                   PCS
90-78496-01010           87-02029-00020                   G
90-78496-01010           02-00002-01011                   G

我想要的结果是：
1.当输入某个PARENT最高层号码比如98-78496-SP417K，得到它下面所有层次的记录：
SQL code


98-78496-SP417K          #98-78496-SP417K                 G
98-78496-SP417K          97-78496-SP417K                  PCS
97-78496-SP417K          96-78496-SP2027                  PCS
97-78496-SP417K          #7678496-00010                   G
97-78496-SP417K          #88-78496-SP2027                 G
96-78496-SP2027          94-78496-SP2027                  PCS
96-78496-SP2027          #87-78496-SP2027                 G
94-78496-SP2027          90-78496-01010                   PCS
90-78496-01010           87-02029-00020                   G
90-78496-01010           02-00002-01011                   G

同时将结果插入到一个表tb中，tb表的格式与BOM1一样。
数据库环境： SQL 2000

作者: wangxiaofeiwuqiao 发布时间: 2011-11-11

测试数据：
SQL code


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

insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('99-88551-01030','97-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-88551-01030','96-88551-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#8003042-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#6300035-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','20-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','21-00001-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','22-00424-01010','G')

insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','#98-78496-SP417K','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','97-78496-SP417K','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','96-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#7678496-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#88-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','94-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','#87-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('94-78496-SP2027','90-78496-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','87-02029-00020','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','02-00002-01011','G')



insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98485-00001','97485-00001','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97485-00001','90485-00001','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90485-00001','#8000-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90485-00001','20485-00001','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20485-00001','21485-00001','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20485-00001','22485-00001','G')

作者: wangxiaofeiwuqiao 发布时间: 2011-11-11

作者: xiaolinyouni 发布时间: 2011-11-11

好长啊先MRAK 开会了回来看。

作者: fredrickhu 发布时间: 2011-11-11

mark个~等楼下

作者: Lastone_Key 发布时间: 2011-11-11

引用 3 楼 fredrickhu 的回复:
好长啊先MRAK 开会了回来看。

那是测试数据多，其实逻辑是比较单一的。只是我是菜菜。

作者: wangxiaofeiwuqiao 发布时间: 2011-11-11

哪个字段代表上下级关系呢

作者: pengxuan 发布时间: 2011-11-11

SQL code

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

insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('99-88551-01030','97-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-88551-01030','96-88551-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#8003042-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#6300035-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','20-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','21-00001-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','22-00424-01010','G')

insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','#98-78496-SP417K','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','97-78496-SP417K','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','96-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#7678496-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#88-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','94-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','#87-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('94-78496-SP2027','90-78496-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','87-02029-00020','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','02-00002-01011','G')



insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98485-00001','97485-00001','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97485-00001','90485-00001','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90485-00001','#8000-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90485-00001','20485-00001','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20485-00001','21485-00001','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20485-00001','22485-00001','G')
GO

DECLARE @PARENT NVARCHAR(50)
SET @PARENT='98-78496-SP417K'

DECLARE @lev INT
SET @lev=0

IF OBJECT_ID('Tempdb..#B') IS NOT NULL
    DROP TABLE #B
SELECT * ,lev=0 INTO #B FROM BOM1 WHERE PARENT=@PARENT  

WHILE @@rowcount>0
BEGIN
SET @lev=@lev+1
INSERT INTO #B 
SELECT   a.*,
lev=b.lev+1  FROM BOM1 AS a 
INNER JOIN #B AS b  on b.ITEM_CODE=a.PARENT AND b.lev=@lev-1

END
SELECT PARENT,ITEM_CODE,BOM_UNIT FROM #B ORDER BY lev

/*
PARENT    ITEM_CODE    BOM_UNIT
98-78496-SP417K    #98-78496-SP417K    G
98-78496-SP417K    97-78496-SP417K    PCS
97-78496-SP417K    96-78496-SP2027    PCS
97-78496-SP417K    #7678496-00010    G
97-78496-SP417K    #88-78496-SP2027    G
96-78496-SP2027    94-78496-SP2027    PCS
96-78496-SP2027    #87-78496-SP2027    G
94-78496-SP2027    90-78496-01010    PCS
90-78496-01010    87-02029-00020    G
90-78496-01010    02-00002-01011    G*/