SQL Server 2000 树状结构
发布网友
发布时间：2022-09-14 15:57
共3个回答
热心网友
时间：2023-12-05 02:16
－－－－
这个。。。。比较复杂，我只写过类似展bom的proceudre，oracle的
但是不知道怎麼说。。。。。
要用到递归，游标
而且bom层数多的话跑起来速度很慢，不小心还会死循环。。。。
简单来说，就是procere一次展一层，比如把需展的成品A展到半成品B，C
然后把半成品B，C当做成品，重复调用自己展下一层DEF。。。直到到底为止（也可以自己设定展到几层就停）
热心网友
时间：2023-12-05 02:16
create table test(id char(1),cid char(1))
go
insert into test select 'A','B'
union all select 'A','C'
union all select 'B','C'
union all select 'B','D'
union all select 'C','E'
union all select 'C','F'
union all select 'D','G'
union all select 'D','H'
union all select 'D','I'
--测试数据
go
create function func(@id char(1))
returns @rs table (id char(1),cid char(1))
as
begin
insert into @rs select * from test where id=@id
while @@rowcount>0
begin
insert into @rs select * from test where id in (select cid from @rs) and id not in (select id from @rs)
end
return
end
--定义函数
go
select * from dbo.func('B')
--运行
go
drop table test
drop function func
热心网友
时间：2023-12-05 02:17
create table bom(bom01_01 nvarchar(10),bom01_02 nvarchar(10))
insert into bom values('A','B')
insert into bom values('A','C')
insert into bom values('B','E')
insert into bom values('E','F')
insert into bom values('B','D')
insert into bom values('F','G')
insert into bom values('1','2')
insert into bom values('2','3')
insert into bom values('2','4')
insert into bom values('4','5')
insert into bom values('3','6')
--创建函数
create function F_digui(@m nvarchar(10))
returns @re table(bom01_01 nvarchar(10),bom01_02 nvarchar(10))
as
begin
declare @sum int,@i int,@s nvarchar(10)
insert @re select * from bom where bom01_01=@m
set @sum=(select count(*) from @re)
set @i=1
if @sum>=1
begin
while @i<=@sum
begin
set @s=(select bom01_02 from (select bom01_02,rank() over(order by bom01_02) as id from @re)a where a.id=@i )
insert @re select * from F_digui(@s)
set @i=@i+1
end
end
return
end
--调用函数
select * from F_digui('1')
--删除测试表和函数
drop function F_digui
drop table bom
***************************
SQL2000的话把函数改成这样：
create function F_digui(@m nvarchar(10))
returns @re table(bom01_01 nvarchar(10),bom01_02 nvarchar(10))
as
begin
declare @sum int,@i int,@s nvarchar(10)
insert @re select * from bom where bom01_01=@m
set @sum=(select count(*) from @re)
set @i=1
if @sum>=1
begin
while @i<=@sum
begin
set @s=(select bom01_02 from (select bom01_02,row_number() over(order by bom01_02) as id from @re)a where a.id=@i )
insert @re select * from F_digui(@s)
set @i=@i+1
end
end
return
end
