create function bom2 (@id char(1))
returns @tb table (x int,partname char(1),parent_name char(1),level int,top1 varchar(20))
as
begin
declare @i int
set @i=0
insert @tb select @i,partname,parent_name,level,@id+partname from 表 where parent_name = @id
while @@rowcount > 0
set @i=@i+1
insert @tb select @i,partname,parent_name,level,
(select top1 from @tb where partname = a.parent_name)+parent_name+partname from 表 a
where parent_name in (select partname from @tb)
and partname not in (select partname from @tb)
return
end
go
select id,parent_name,partname,level,top1,x from dbo.bom('A')
order by top1
returns @tb table (x int,partname char(1),parent_name char(1),level int,top1 varchar(20))
as
begin
declare @i int
set @i=0
insert @tb select @i,partname,parent_name,level,@id+partname from 表 where parent_name = @id
while @@rowcount > 0
set @i=@i+1
insert @tb select @i,partname,parent_name,level,
(select top1 from @tb where partname = a.parent_name)+parent_name+partname from 表 a
where parent_name in (select partname from @tb)
and partname not in (select partname from @tb)
return
end
go
select id,parent_name,partname,level,top1,x from dbo.bom('A')
order by top1
不明白你说的道理,是否可仔细一点。
我现在的问题是第二种方法算出来,少了一条记录,你明白吗
returns @tb table (x int,partname char(1),parent_name char(1),level int,top1 varchar(20))
as
begin
declare @i int
set @i=0
insert @tb select @i,partname,parent_name,level,@id+partname from 表 where parent_name = @id
while @@rowcount > 0
begin
set @i=@i+1
insert @tb select @i,partname,parent_name,level,
(select top1 from @tb where partname = a.parent_name)+parent_name+partname from 表 a
where parent_name in (select partname from @tb)
and partname not in (select partname from @tb)
end
return
end
go
select id,parent_name,partname,level,top1,x from dbo.bom('A')
order by top1
returns @tb table (x int,partname char(1),parent_name char(1),level int,top1 varchar(20))
as
begin
declare @i int
set @i=0
insert @tb select @i,partname,parent_name,level,@id+partname from 表 where parent_name = @id
while @@rowcount > 0
begin <<<===========你少写了
set @i=@i+1
insert @tb select @i,partname,parent_name,level,
(select top1 from @tb where partname = a.parent_name)+parent_name+partname from 表 a
where parent_name in (select partname from @tb)
and partname not in (select partname from @tb)
end
return
end
go
select id,parent_name,partname,level,top1,x from dbo.bom('A')
order by top1
非常感谢,想与你交个朋友!
msn:[email protected]