本人有一树型结构,例如：
a id1 id2
/ \ ----------------
b e 在数据库中的结构--> * a
/ \ \ a b
c d f b c
b d
a e
e f
- 1
其中'*'代表根结点 1 2
2 3
有没有可能利用一句（或较简单）sql语句遍历该树，得到如下结果：
abc
abd
aef
123
---------------------------------------------------------------
可以用递归来实现，一句语句恐怕不行
---------------------------------------------------------------
不太可能，帮你UP一下
---------------------------------------------------------------
一句语句不太可能，但可以构造个函数．
换个思维方式，如果从每个最终的叶节点反向倒推，语句可能简单的多
select * from xxx
where id2 not in (select id1 from xxx)
找出所有的最终叶节点，反向倒推
---------------------------------------------------------------
提供几个函数，供参考：
CREATE function isGrandFather(@id1 varchar(20),@id2 varchar(20))
returns bit
as
begin
declare @i int, @flag varchar(20)
set @flag=0
while(@id2 is not null and dbo.nodeparent(@id2) is not null)
begin
if (dbo.nodeparent(@id2)!=@id1)
set @id2=dbo.nodeparent(@id2)
else
begin
set @flag=1
break
end
end
return @flag
end
CREATE FUNCTION isLeaf(@id varchar(20))
returns bit
as
begin
declare @flag bit
set @flag=0
if exists(select * from table1 where id1=@id)
set @flag=1
return @flag
end
CREATE FUNCTION nodeparent(@id varchar(20))
returns varchar(20)
as
begin
declare @parent varchar(20)
set @parent=(select top 1 id1 from table1 where id2=@id)
return @parent
end
---------------------------------------------------------------
drop table new_tree
go
/parent对应父亲结点，child对应儿子结点，如果child is NULL则本结点为叶子结点/
create table new_tree ( parent varchar(80), child varchar(80))
go
insert new_tree values ( '1','2');
insert new_tree values ( '1','3');
insert new_tree values ( '2','4');
insert new_tree values ( '2','5');
insert new_tree values ( '3','6');
insert new_tree values ( '3','7');
insert new_tree values ( '3','8');
insert new_tree values ( '6','9');
insert new_tree values ( '5','10');
insert new_tree values ( '4','11');
insert new_tree values ( '9','12');
insert new_tree values ( '7',NULL);
insert new_tree values ( '8',NULL);
insert new_tree values ( '10',NULL);
insert new_tree values ( '11',NULL);
insert new_tree values ( '12',NULL);
drop proc proc_new_tree
go
/@parent 输入根结点标识，@mode为0 则输出为所有子孙记录，否则输出所有叶子结点/
create proc proc_new_tree (@parent varchar(80),@mode int =0)
as
begin
set nocount on
/如果不是SQLSERVER2000可以用临时表/
declare @tmp1 table ( parent varchar(80), child varchar(80),trace varchar(256))
declare @tmp2 table ( parent varchar(80), child varchar(80),trace varchar(256))
declare @tmp3 table ( parent varchar(80), child varchar(80),trace varchar(256))
insert @tmp1 select parent,child,@parent from new_tree where parent = @parent
insert @tmp3 select parent,child,@parent from new_tree where parent = @parent
/循环的次数等于树的深度/
while exists(select * from @tmp1 where child is not NULL)
begin
insert @tmp2 select a.parent,a.child , b.trace+'->'+a.parent from new_tree a,@tmp1 b where a.parent = b.child
/@tmp2表中存本次查询的层次的所有结点/
delete from @tmp1 where child is not NULL
/@tmp1表中最终存的是叶子结点/
insert @tmp1 select * from @tmp2
/@tmp3表中最保存每次查到的子孙/
insert @tmp3 select * from @tmp2
delete from @tmp2
end
if @mode =0 select * from @tmp3
else select trace from @tmp1
set nocount off
end
go
proc_new_tree '1',1
go