各位好,
最近遇到一个树形结构的问题,想找一个效率高点的办法,因为数据量很大,只要能实现遍历,不管是前台,还是后台,只要效率高,就可以,
数据表结构如下:
ParentID PartNo LinkQty HasChild
1 A 1 True
1 B 2 True
1 C 4 True
A A1 2 True
B B1 23 True
C E 3 True
B E 5 True
......
要求:
1.已知一节点,求一个遍历此节点下所有的元素的算法,递归也可以
2.考虑效率
3.请给出例子
---------------------------------------------------------------
drop table tree
go
create table tree ( node int, child int, weight int)
go
insert into tree values (1,2,5)
insert into tree values (1,3,5)
insert into tree values (2,4,6)
insert into tree values (2,5,6)
insert into tree values (2,6,6)
insert into tree values (3,7,6)
insert into tree values (3,8,6)
insert into tree values (4,0,6)
insert into tree values (5,9,6)
insert into tree values (5,10,6)
insert into tree values (6,0,4)
insert into tree values (7,11,1)
insert into tree values (8,0,4)
insert into tree values (9,0,1)
insert into tree values (10,0,5)
insert into tree values (11,0,5)
--child为0代表是叶子节点
go
drop proc proc_tree
go
create proc proc_tree (@node int)
as
begin
set nocount on
create table #tmp1 ( node int, parent int)
create table #tmp2 ( node int, parent int)
insert #tmp1 select node,child from tree where node = @node
while exists(select * from #tmp1 where parent <> 0)
begin
insert #tmp2 select a.node,a.child from tree a,#tmp1 b where a.node = b.parent
delete from #tmp1 where parent <> 0
insert #tmp1 select * from #tmp2
truncate table #tmp2
end
select * from #tmp1
set nocount off
end
go
proc_tree 1