在数据库表中插入具有上下级关系的(树形结构)数据记录是一件很平常的事,但要再从表中取出(父子孙关系)树形关系的数据时就变得不方便了,以下是一种利用临时表查询出数据的一个存储过程:
create table Tree
(ID int identity(1,1),
Tree_up int,
Tree_dn int)
go
===>插入一些数据
create proc sp_Tree
@Tree_up int ,@Branch int output
as
set nocount on
declare @l int
set @l = 1
create table #table(Tree_up int,Tree_dn int,leval int)
insert #table select Tree_up ,Tree_dn,@l from Tree where Tree_up=@Tree_up
while @@rowcount>0
begin
set @l=@l+1
insert #table select b.Tree_dn,a.Tree_dn from #table b,Tree a where b.Tree_dn = a.Tree_up and b.leval=@l-1
end
select distinct Tree_dn from #table
select @Branch=Max(@l)
go
declare @b int
exec sp_Tree @Tree_up @Branch=@b output
select @b