帮助写一个递归程序,好吗?(在线等:QQ:13725905)

[sandy_live]

是这样:
数据库如下:
id itemname parentid
1 test1 0
2 test2 0
3 test3 1
4 test4 1
5 test5 3
6 test6 5
0代表为第一级.parentid 为前面的id号
输入的格式为:
test1 test2
|
test3 test4
|
test5
|
test6
请给出一个程序好吗?在此先谢谢各位.最好写一个通用的程序

[Chiff]

看老衲的公告，关于树的遍历：）

[CrazyFor]

我的一个展BOM的递归存储过程，看看吧，结构相似。

CREATE PROCEDURE BD_EB_New
@Item_No varchar(50),
@Component_No varchar(50),
@ComponentQty decimal(30,15),
@IntLevel int

as

SET NOCOUNT ON




Declare @ItemNo_T varchar(50),@ComponentNo_T varchar(50),@ComponentQty_T float(8),@TheFirst bit,@TempStr VARCHAR(800),@qty_tmp decimal(30,15)

SELECT @TempStr=REPLICATE('| ', @IntLevel-1)

select @ComponentQty_T=1


DECLARE @1Item_No varchar(50),
@1Component_No varchar(50),
@1Qtyper varchar(50),
@1IsProduct bit,
@1IsHalfProduct bit,
@1IsMaterial bit


if @Item_No='' and @Component_No=''
Begin
select @TheFirst=1
DECLARE Bom_Product CURSOR local FOR
SELECT Item_No,Component_No,Qtyper,IsProduct,IsHalfProduct,IsMaterial
FROM Mrp_bomdetailBak

OPEN Bom_Product

FETCH NEXT FROM Bom_Product
INTO @1Item_No,@1Component_No,@1Qtyper,@1IsProduct,@1IsHalfProduct,@1IsMaterial
End
else
Begin
select @TheFirst=0
DECLARE Bom_Product CURSOR local FOR
SELECT Item_No,Component_No,Qtyper,IsProduct,IsHalfProduct,IsMaterial
FROM Mrp_bomdetailBak
where Item_No=@Component_No


OPEN Bom_Product

FETCH NEXT FROM Bom_Product
INTO @1Item_No,@1Component_No,@1Qtyper,@1IsProduct,@1IsHalfProduct,@1IsMaterial
End

WHILE @@FETCH_STATUS = 0
BEGIN
if (@TheFirst=1) AND (@Item_No<>@1Item_No)
begin
Select @Item_No=@1Item_No
SELECT @IntLevel=1
Print ''
Print'-----------------------------------------------'+ cast(@1Item_No as varchar) +'-----'
end
if @1IsHalfProduct=1
Begin
Print @TempStr+'| +'+cast(@1Item_No as varchar) + cast(@1Qtyper as varchar) + '->'+@1Component_No

select @ComponentNo_T=@1Component_No
--sam add
select @qty_tmp=@ComponentQty*@1Qtyper
--select @1Qtyper=@ComponentQty_T*@1Qtyper
--end

select @ComponentQty_T=@1Qtyper


select @IntLevel=@IntLevel+1
--exec BD_EB_New @Item_No , @ComponentNo_T , @ComponentQty_T,@IntLevel
exec BD_EB_New @Item_No , @ComponentNo_T , @qty_tmp,@IntLevel


select @IntLevel=@IntLevel-1

End
Else
begin
Print @TempStr+'|-'+cast(@1Item_No as varchar) + cast(@1Qtyper as varchar) + '***' + cast(@ComponentQty*@1Qtyper as varchar) +'->'+@1Component_No
insert into Mrp_bomdetail_EXP (item_no,COMPONENT_NO,QTYPER) values (@Item_No,@1Component_No,@ComponentQty*@1Qtyper)
end

FETCH NEXT FROM Bom_Product
INTO @1Item_No,@1Component_No,@1Qtyper,@1IsProduct,@1IsHalfProduct,@1IsMaterial

End

CLOSE Bom_Product
DEALLOCATE Bom_Product
GO