有表BOM1：其中PARENT与ITEM_CODE可以存在不定的多层的递归关系，层层递归，99下面是98，98下是97,97下是94,94下是91，91下面是20或者21,22等等。例如：
- SQL code
PARENT(成品) ITEM_CODE(半成品或物料) BOM_UNIT99-88551-01030 97-88551-01030 PCS ----此为最高层97-88551-01030 96-88551-01010 PCS96-88551-01010 #8003042-01010 G96-88551-01010 #6300035-01010 G96-88551-01010 20-88551-01030 PCS20-88551-01030 21-00001-00010 G20-88551-01030 22-00424-01010 G98485-00001 97485-00001 PCS ----此为最高层97485-00001 90485-00001 PCS90485-00001 #8000-01010 G90485-00001 20485-00001 PCS20485-00001 21485-00001 PCS20485-00001 22485-00001 G98-78496-SP417K #98-78496-SP417K G ----此为最高层98-78496-SP417K 97-78496-SP417K PCS97-78496-SP417K 96-78496-SP2027 PCS97-78496-SP417K #7678496-00010 G97-78496-SP417K #88-78496-SP2027 G96-78496-SP2027 94-78496-SP2027 PCS96-78496-SP2027 #87-78496-SP2027 G94-78496-SP2027 90-78496-01010 PCS90-78496-01010 87-02029-00020 G90-78496-01010 02-00002-01011 G
我想要的结果是：
1.当输入某个PARENT最高层号码比如98-78496-SP417K，得到它下面所有层次的记录：
- SQL code
98-78496-SP417K #98-78496-SP417K G98-78496-SP417K 97-78496-SP417K PCS97-78496-SP417K 96-78496-SP2027 PCS97-78496-SP417K #7678496-00010 G97-78496-SP417K #88-78496-SP2027 G96-78496-SP2027 94-78496-SP2027 PCS96-78496-SP2027 #87-78496-SP2027 G94-78496-SP2027 90-78496-01010 PCS90-78496-01010 87-02029-00020 G90-78496-01010 02-00002-01011 G
同时将结果插入到一个表tb中，tb表的格式与BOM1一样。
数据库环境： SQL 2000
------解决方案--------------------
- 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')GODECLARE @PARENT NVARCHAR(50)SET @PARENT='98-78496-SP417K'DECLARE @lev INTSET @lev=0IF OBJECT_ID('Tempdb..#B') IS NOT NULL DROP TABLE #BSELECT * ,lev=0 INTO #B FROM BOM1 WHERE PARENT=@PARENT WHILE @@rowcount>0BEGINSET @lev=@lev+1INSERT 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-1ENDSELECT PARENT,ITEM_CODE,BOM_UNIT FROM #B ORDER BY lev/*PARENT ITEM_CODE BOM_UNIT98-78496-SP417K #98-78496-SP417K G98-78496-SP417K 97-78496-SP417K PCS97-78496-SP417K 96-78496-SP2027 PCS97-78496-SP417K #7678496-00010 G97-78496-SP417K #88-78496-SP2027 G96-78496-SP2027 94-78496-SP2027 PCS96-78496-SP2027 #87-78496-SP2027 G94-78496-SP2027 90-78496-01010 PCS90-78496-01010 87-02029-00020 G90-78496-01010 02-00002-01011 G*/