图示如下：所使用数据库 ： SQL Server 2000，数据表名：Te（乱七八糟命名的）表结构： varchar(20) varchar(20) decimal(20,4) Assbompoint sl  产成品半成品12  产成品半成品2 3  产成品半成品3 2  半成品1半成品4 2  半成品1半成品5 2  半成品3半成品5 3   


最终需要输出结果

 Assbompoint sl  产成品 半成品23  产成品 半成品44 产成品 半成品513 

即一单位产成品需要底级半成品的数量以及半成品的名称。比如说半成品4=1单位产成品*2单位半成品1*2单位半成品4。



存储过程如下：（呵呵，命名就别讲究了，我没有太过于留意）

create procedure GetBomTrueList
 @AssBomName varchar(20),@pointName varchar(20),@expre varchar(500)
as
begin
 declare @sl decimal(20,4)
 declare @expression varchar(500)
 declare @point varchar(20)

 declare @bomTop varchar(20)
 declare #point_cursor cursor LOCAL for
  select distinct point,sl from te where Assbom  = @pointName
 open #point_cursor

 fetch next from #point_cursor
  into @point,@sl

 while @@FETCH_STATUS = 0
 begin
  --如果没有下级节点了，就加入到数据表中
  if(exists(select point from te where Assbom = @point))
  begin
   --如果有下级节点，则再次循环
   select @expression = rtrim(@expre) + '*' + rtrim(convert(char(25),@sl))
   exec('GetBomTrueList ' + @AssBomName + ',' + @point + ',"' + @expression + '"')
  end
  else
  begin
   insert into #bomTemp values (@AssBomName,@point,@sl,@expre + '*' + ltrim(rtrim(convert(char(25),@sl))))
  end
  
  fetch next from #point_cursor
   into @point,@sl
 end

 close #point_cursor
 deallocate #point_cursor
end
go

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

create procedure GetBomList
as

begin

 create table #bomTemp (Assbom varchar(20),point varchar(20),sl decimal(20,4),expression varchar(500))
 create table #bomLast (Assbom varchar(20),point varchar(20),sl decimal(20,4))

 --调用递归
 declare @bomTop varchar(20)
 declare bom_cursor cursor for
  select distinct Assbom from te where Assbom not in (select point from te)

 open bom_cursor

 fetch next from bom_cursor
  into @bomTop

 while @@FETCH_STATUS = 0
 begin
  Exec('GetBomTrueList ' + @bomTop +','+@bomTop+','+'1')

  fetch next from bom_cursor
   into @bomTop
 end

 close bom_cursor
 deallocate bom_cursor

 --获得到数据及运算表达式后，再次利用数据表中的表达式运算一下
 declare @AssbomT varchar(20)
 declare @point varchar(20)
 declare @expression varchar(500)
 declare @value decimal(20,4)

 declare @execUpdate varchar(500)


 declare bom_cursor_end cursor for
  select Assbom,point,expression from #bomTemp
 open bom_cursor_end
 fetch next from bom_cursor_end
  into @AssbomT,@point,@expression
 while @@FETCH_STATUS = 0
 begin
  set @execUpdate = 'Insert into #bomLast values(' + char(39) + rtrim(@AssbomT) + char(39) + ',' + char(39) + rtrim(@point) + char(39) + ',' + rtrim(@expression) + ')'
  --set @execUpdate = 'update #bomTemp set sl = ' + rtrim(@expression) + ' where Assbom = ' + char(39) + rtrim(@AssbomT) + char(39) + ' and point = ' + char(39) + rtrim(@point) + char(39)
  exec(@execUpdate)
  fetch next from bom_cursor_end
   into @AssbomT,@point,@expression
 end

 close bom_cursor_e