递归又来了！思路明确

wangxiaofeiwuqiao 2011-11-29 02:53:28
有表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

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


我想要的结果：想通过表tb1一次性查找它底层的所有物料，半成品，成品。不管有没有重复的，都将结果写进一个表：


99-88551-01030           --99-88551-01030 下面的所有物料

97-88551-01030                   

97-88551-01030           

96-88551-01010                   

96-88551-01010           

#8003042-01010                  

96-88551-01010           

#6300035-01010                   

96-88551-01010           

20-88551-01030                   

20-88551-01030           

21-00001-00010                   

20-88551-01030

22-00424-01010                   

20-88551-01030



98-78496-SP417K          --98-78496-SP417K 下面的所有物料

#98-78496-SP417K                 

98-78496-SP417K          

97-78496-SP417K                  

97-78496-SP417K          

96-78496-SP2027                  

97-78496-SP417K          

#7678496-00010                   

97-78496-SP417K          

#88-78496-SP2027                 

96-78496-SP2027          

94-78496-SP2027                  

96-78496-SP2027          

#87-78496-SP2027                 

94-78496-SP2027          

90-78496-01010                   

90-78496-01010           

87-02029-00020                   

90-78496-01010           

02-00002-01011


数据库环境： SQL 2000
参考类似贴：http://topic.csdn.net/u/20111111/15/faeb68e1-195c-4492-8371-0465c4b1beed.html
...全文
259 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
wangxiaofeiwuqiao 2012-02-02
  • 打赏
  • 举报
 回复
采用14楼方法。
wangxiaofeiwuqiao 2011-11-29
  • 打赏
  • 举报
 回复
两位好心的大哥方法都可以。回去详细测试后再结贴。谢谢。
中国风 2011-11-29
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 wangxiaofeiwuqiao 的回复:]

你们把我的tb1表弄哪里去了？只拆分tb1里面存着的成品
[/Quote]
加上這一段
AND EXISTS(SELECT 1 FROM tb1 WHERE ITEM_CODE=a.PARENT)


IF OBJECT_ID('BOM1') IS NOT NULL

	DROP TABLE BOM1

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 @lev INT

SET @lev=0



IF OBJECT_ID('Tempdb..#B') IS NOT NULL

    DROP TABLE #B

SELECT ID=identity(int,1,1), a.PARENT,a.ITEM_CODE,Product=PARENT,lev=0 

INTO #B 

FROM BOM1 AS a 

WHERE NOT EXISTS(SELECT 1 FROM BOM1 WHERE ITEM_CODE=a.PARENT ) 

AND EXISTS(SELECT 1 FROM tb1 WHERE ITEM_CODE=a.PARENT)



WHILE @@rowcount>0

BEGIN

	SET @lev=@lev+1

	INSERT INTO #B 

	SELECT   

		a.PARENT,a.ITEM_CODE,Product=b.Product,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,Product

FROM 

(

SELECT ID,PARENT,Product,lev,1 AS ord

FROM #B 

UNION ALL 

SELECT ID,ITEM_CODE,Product,lev,2 AS ord

FROM #B 

)t

ORDER BY Product,ID,ord



/*

PARENT	Product

98485-00001	98485-00001

97485-00001	98485-00001

97485-00001	98485-00001

90485-00001	98485-00001

90485-00001	98485-00001

#8000-01010	98485-00001

90485-00001	98485-00001

20485-00001	98485-00001

20485-00001	98485-00001

21485-00001	98485-00001

20485-00001	98485-00001

22485-00001	98485-00001

98-78496-SP417K	98-78496-SP417K

#98-78496-SP417K	98-78496-SP417K

98-78496-SP417K	98-78496-SP417K

97-78496-SP417K	98-78496-SP417K

97-78496-SP417K	98-78496-SP417K

96-78496-SP2027	98-78496-SP417K

97-78496-SP417K	98-78496-SP417K

#7678496-00010	98-78496-SP417K

97-78496-SP417K	98-78496-SP417K

#88-78496-SP2027	98-78496-SP417K

96-78496-SP2027	98-78496-SP417K

94-78496-SP2027	98-78496-SP417K

96-78496-SP2027	98-78496-SP417K

#87-78496-SP2027	98-78496-SP417K

94-78496-SP2027	98-78496-SP417K

90-78496-01010	98-78496-SP417K

90-78496-01010	98-78496-SP417K

87-02029-00020	98-78496-SP417K

90-78496-01010	98-78496-SP417K

02-00002-01011	98-78496-SP417K

99-88551-01030	99-88551-01030

97-88551-01030	99-88551-01030

97-88551-01030	99-88551-01030

96-88551-01010	99-88551-01030

96-88551-01010	99-88551-01030

#8003042-01010	99-88551-01030

96-88551-01010	99-88551-01030

#6300035-01010	99-88551-01030

96-88551-01010	99-88551-01030

20-88551-01030	99-88551-01030

20-88551-01030	99-88551-01030

21-00001-00010	99-88551-01030

20-88551-01030	99-88551-01030

22-00424-01010	99-88551-01030

*/
Level_Meng 2011-11-29
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 acherat 的回复:]

插入到别的表可以把

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

改为

INSERT INTO 表 SELECT PARENT FROM #B ORDER BY lev

这样。
[/Quote]

gg
AcHerat 元老 2011-11-29
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 wangxiaofeiwuqiao 的回复:]
你们把我的tb1表弄哪里去了？只拆分tb1里面存着的成品
[/Quote]

这个你建表没有tb1，我们以为就只按bom1了。。。



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

set nocount on

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



create table tb1(ITEM_CODE varchar(50))

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

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

set nocount off

go



DECLARE @lev INT

SET @lev=0



IF OBJECT_ID('Tempdb..#B') IS NOT NULL

    DROP TABLE #B

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 PARENT,PG FROM #B

UNION ALL

SELECT ITEM_CODE,PG as fg FROM #B t where not exists (select 1 from #B where PARENT=t.ITEM_CODE)

ORDER BY PG,PARENT



drop table tb1,bom1,#B



/*************



PARENT                                             PG

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

#7678496-00010                                     98-78496-SP417K

#87-78496-SP2027                                   98-78496-SP417K

#88-78496-SP2027                                   98-78496-SP417K

#98-78496-SP417K                                   98-78496-SP417K

02-00002-01011                                     98-78496-SP417K

87-02029-00020                                     98-78496-SP417K

90-78496-01010                                     98-78496-SP417K

90-78496-01010                                     98-78496-SP417K

94-78496-SP2027                                    98-78496-SP417K

96-78496-SP2027                                    98-78496-SP417K

96-78496-SP2027                                    98-78496-SP417K

97-78496-SP417K                                    98-78496-SP417K

97-78496-SP417K                                    98-78496-SP417K

97-78496-SP417K                                    98-78496-SP417K

98-78496-SP417K                                    98-78496-SP417K

98-78496-SP417K                                    98-78496-SP417K

#6300035-01010                                     99-88551-01030

#8003042-01010                                     99-88551-01030

20-88551-01030                                     99-88551-01030

20-88551-01030                                     99-88551-01030

21-00001-00010                                     99-88551-01030

22-00424-01010                                     99-88551-01030

96-88551-01010                                     99-88551-01030

96-88551-01010                                     99-88551-01030

96-88551-01010                                     99-88551-01030

97-88551-01030                                     99-88551-01030

99-88551-01030                                     99-88551-01030



(27 行受影响)
wangxiaofeiwuqiao 2011-11-29
  • 打赏
  • 举报
 回复
wangxiaofeiwuqiao 2011-11-29
  • 打赏
  • 举报
 回复
你们把我的tb1表弄哪里去了？只拆分tb1里面存着的成品
AcHerat 元老 2011-11-29
  • 打赏
  • 举报
 回复
5楼这个语句是多余的

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

你把这个去了就好了，结果一样的。
中国风 2011-11-29
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 wangxiaofeiwuqiao 的回复:]

引用 5 楼 acherat 的回复:
SQL code

--貌似上边有漏掉的，这个呢？

create table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),BOM_UNIT varchar(50))
set nocount on
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT)
valu……
[/Quote]

加多一欄記錄成品，不然你區分不了，結果集是屬於那一個成品的
wangxiaofeiwuqiao 2011-11-29
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 acherat 的回复:]
SQL code

--貌似上边有漏掉的，这个呢？

create table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),BOM_UNIT varchar(50))
set nocount on
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT)
values('99-88551-01030','……
[/Quote]
不需要输入某一个固定的成品查询，我想用sql 语句一次性把tb1表里面所有的成品拆分
中国风 2011-11-29
  • 打赏
  • 举报
 回复
是不是應該加一欄Product，這樣才能區分是那一個成品



IF OBJECT_ID('BOM1') IS NOT NULL

	DROP TABLE BOM1

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 @lev INT

SET @lev=0



IF OBJECT_ID('Tempdb..#B') IS NOT NULL

    DROP TABLE #B

SELECT ID=identity(int,1,1), a.PARENT,a.ITEM_CODE,Product=PARENT,lev=0 

INTO #B 

FROM BOM1 AS a WHERE NOT EXISTS(SELECT 1 FROM BOM1 WHERE ITEM_CODE=a.PARENT ) 



WHILE @@rowcount>0

BEGIN

	SET @lev=@lev+1

	INSERT INTO #B 

	SELECT   

		a.PARENT,a.ITEM_CODE,Product=b.Product,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,Product

FROM 

(

SELECT ID,PARENT,Product,lev,1 AS ord

FROM #B 

UNION ALL 

SELECT ID,ITEM_CODE,Product,lev,2 AS ord

FROM #B 

)t

ORDER BY Product,ID,ord



/*

PARENT	Product

98485-00001	98485-00001

97485-00001	98485-00001

97485-00001	98485-00001

90485-00001	98485-00001

90485-00001	98485-00001

#8000-01010	98485-00001

90485-00001	98485-00001

20485-00001	98485-00001

20485-00001	98485-00001

21485-00001	98485-00001

20485-00001	98485-00001

22485-00001	98485-00001

98-78496-SP417K	98-78496-SP417K

#98-78496-SP417K	98-78496-SP417K

98-78496-SP417K	98-78496-SP417K

97-78496-SP417K	98-78496-SP417K

97-78496-SP417K	98-78496-SP417K

96-78496-SP2027	98-78496-SP417K

97-78496-SP417K	98-78496-SP417K

#7678496-00010	98-78496-SP417K

97-78496-SP417K	98-78496-SP417K

#88-78496-SP2027	98-78496-SP417K

96-78496-SP2027	98-78496-SP417K

94-78496-SP2027	98-78496-SP417K

96-78496-SP2027	98-78496-SP417K

#87-78496-SP2027	98-78496-SP417K

94-78496-SP2027	98-78496-SP417K

90-78496-01010	98-78496-SP417K

90-78496-01010	98-78496-SP417K

87-02029-00020	98-78496-SP417K

90-78496-01010	98-78496-SP417K

02-00002-01011	98-78496-SP417K

99-88551-01030	99-88551-01030

97-88551-01030	99-88551-01030

97-88551-01030	99-88551-01030

96-88551-01010	99-88551-01030

96-88551-01010	99-88551-01030

#8003042-01010	99-88551-01030

96-88551-01010	99-88551-01030

#6300035-01010	99-88551-01030

96-88551-01010	99-88551-01030

20-88551-01030	99-88551-01030

20-88551-01030	99-88551-01030

21-00001-00010	99-88551-01030

20-88551-01030	99-88551-01030

22-00424-01010	99-88551-01030

*/
AcHerat 元老 2011-11-29
  • 打赏
  • 举报
 回复 


--貌似上边有漏掉的，这个呢？



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

set nocount on

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')

set nocount off

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,PARENT AS PG INTO #B FROM BOM1 t WHERE not exists (select 1 from BOM1 where ITEM_CODE = t.PARENT)



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 PARENT,lev,PG,0 as fg FROM #B

UNION ALL

SELECT ITEM_CODE,lev,PG,1 as fg FROM #B t where not exists (select 1 from #B where PARENT=t.ITEM_CODE)

ORDER BY PG,lev,fg



drop table bom1,#B





/*******************



PARENT                                             lev         PG                                                 fg

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

98485-00001                                        0           98485-00001                                        0

97485-00001                                        1           98485-00001                                        0

90485-00001                                        2           98485-00001                                        0

90485-00001                                        2           98485-00001                                        0

#8000-01010                                        2           98485-00001                                        1

20485-00001                                        3           98485-00001                                        0

20485-00001                                        3           98485-00001                                        0

21485-00001                                        3           98485-00001                                        1

22485-00001                                        3           98485-00001                                        1

98-78496-SP417K                                    0           98-78496-SP417K                                    0

98-78496-SP417K                                    0           98-78496-SP417K                                    0

#98-78496-SP417K                                   0           98-78496-SP417K                                    1

97-78496-SP417K                                    1           98-78496-SP417K                                    0

97-78496-SP417K                                    1           98-78496-SP417K                                    0

97-78496-SP417K                                    1           98-78496-SP417K                                    0

#7678496-00010                                     1           98-78496-SP417K                                    1

#88-78496-SP2027                                   1           98-78496-SP417K                                    1

96-78496-SP2027                                    2           98-78496-SP417K                                    0

96-78496-SP2027                                    2           98-78496-SP417K                                    0

#87-78496-SP2027                                   2           98-78496-SP417K                                    1

94-78496-SP2027                                    3           98-78496-SP417K                                    0

90-78496-01010                                     4           98-78496-SP417K                                    0

90-78496-01010                                     4           98-78496-SP417K                                    0

87-02029-00020                                     4           98-78496-SP417K                                    1

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

99-88551-01030                                     0           99-88551-01030                                     0

97-88551-01030                                     1           99-88551-01030                                     0

96-88551-01010                                     2           99-88551-01030                                     0

96-88551-01010                                     2           99-88551-01030                                     0

96-88551-01010                                     2           99-88551-01030                                     0

#8003042-01010                                     2           99-88551-01030                                     1

#6300035-01010                                     2           99-88551-01030                                     1

20-88551-01030                                     3           99-88551-01030                                     0

20-88551-01030                                     3           99-88551-01030                                     0

21-00001-00010                                     3           99-88551-01030                                     1

22-00424-01010                                     3           99-88551-01030                                     1



(36 行受影响)
AcHerat 元老 2011-11-29
  • 打赏
  • 举报
 回复
插入到别的表可以把

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

改为

INSERT INTO 表 SELECT PARENT FROM #B ORDER BY lev

这样。
AcHerat 元老 2011-11-29
  • 打赏
  • 举报
 回复 


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

set nocount on

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')

set nocount off

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 t WHERE not exists (select 1 from BOM1 where ITEM_CODE = t.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



drop table bom1,#B



/*****************



PARENT                                             ITEM_CODE                                          BOM_UNIT

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

99-88551-01030                                     97-88551-01030                                     PCS

98-78496-SP417K                                    #98-78496-SP417K                                   G

98-78496-SP417K                                    97-78496-SP417K                                    PCS

98485-00001                                        97485-00001                                        PCS

97485-00001                                        90485-00001                                        PCS

97-78496-SP417K                                    96-78496-SP2027                                    PCS

97-78496-SP417K                                    #7678496-00010                                     G

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

97-88551-01030                                     96-88551-01010                                     PCS

90485-00001                                        #8000-01010                                        PCS

90485-00001                                        20485-00001                                        PCS

96-78496-SP2027                                    94-78496-SP2027                                    PCS

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

96-88551-01010                                     #8003042-01010                                     G

96-88551-01010                                     #6300035-01010                                     G

96-88551-01010                                     20-88551-01030                                     PCS

20485-00001                                        21485-00001                                        G

20485-00001                                        22485-00001                                        G

20-88551-01030                                     21-00001-00010                                     G

20-88551-01030                                     22-00424-01010                                     G

94-78496-SP2027                                    90-78496-01010                                     PCS

90-78496-01010                                     87-02029-00020                                     G

90-78496-01010                                     02-00002-01011                                     G



(23 行受影响)
--小F-- 2011-11-29
  • 打赏
  • 举报
 回复
等晴天大大或者大版算了 好像这个一直是他们在做。
wangxiaofeiwuqiao 2011-11-29
  • 打赏
  • 举报
 回复
BOM1测试数据：


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')
Python递归算法详解
Python递归算法详解 2018.08.05 17:501296浏览 递归的概念很简单，如果函数包含了对其自身的调用，该函数就是递归的。 递归（Recursion），在数学与计算机科学中，是指在函数的定义中使用函数自身的方法。 在使用递归时，需要注意以下几点： 递归就是在过程或函数里调用自身 必须有一个明确的递归结束条件，称为递归出口。 注意： 切勿忘记递归出口，避免函数无限调用...
关于递归的理解
关于递归的理解1.递归的理解1）递归的概念2）递归的三大要素①明确函数要干什么②寻找递归结束条件③找出函数的等价关系式④举些例子2.递归之八皇后问题理解3.递归的优化1）尾调用2）memoization3）函数式编程（Java描述）：尾调用及抽象递归4.递归的调用机制（简单版）5.递归的调用机制（复杂版）1）基于栈的本质2）递归的工作栈3）递归的底层 1.递归的理解 1）递归的概念 一种计算过程，如果其中每一步都要用到前一步或前几步的结果，称为递归的。用递归过程定义的函数，称为递归函数，例如连加、连乘及阶乘
python递归必须要有_Python的递归
递归是指函数/过程/子程序在运行过程序中直接或间接调用自身而产生的重入现象。在计算机编程里，递归指的是一个过程：函数不断引用自身，直到引用的对象已知。使用递归解决问题，思路清晰，代码少。但是在主流高级语言中(如C语言、Pascal语言等)使用递归算法要耗用更多的栈空间，所以在堆栈尺寸受限制时(如嵌入式系统或者内核态编程)，应避免采用。所有的递归算法都可以改写成与之等价的非递归算法。什么是函数递归函...
递归算法图文详细总结，收藏了！
前言递归是算法中一种非常重要的思想，应用也很广，小到阶乘,再在工作中用到的比如统计文件夹大小，大到 Google 的 PageRank 算法都能看到，也是面试官很喜欢的考点最近看了不少递...
【算法详解】如何使用递归，递归使用的技巧详解
先来看下百度百科的定义：程序调用自身的编程技巧称为递归（ recursion）。递归作为一种算法在程序设计语言中广泛应用。一个过程或函数在其定义或说明中有直接或间接调用自身的一种方法，它通常把一个大型复杂的问题层层转化为一个与原问题相似的规模较小的问题来求解，递归策略只需少量的程序就可描述出解题过程所需要的多次重复计算，大大地减少了程序的代码量。递归的能力在于用有限的语句来定义对象的无限集合。一般来说，递归需要有边界条件、递归前进段和递归返回段。当边界条件不满足时，递归前进；当边界条件满足时，递归返回。
MS-SQL Server

34,595

社区成员

254,590

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章