create table t_tree(ID int, ParentID int) insert t_tree select 1, -1 union all select 2, 1 union all select 3, 1 union all select 4, 2 union all select 5, 2 union all select 6, 3 如下:create function roy_b(@r int) RETURNS @ta table(id int,parentid int) as begin While Exists(Select 1 From t_tree Where id=@r and parentid<>0) begin insert @ta select * from t_tree where id=@r select @r=parentid from t_tree where id=@r end RETURN endselect * from dbo.roy_b(5) 效果如下: id parentid ----------- ----------- 5 2 2 1 1 -1(所影响的行数为 3 行)
insert t_tree
select 1, -1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 2 union all
select 5, 2 union all
select 6, 3
如下:create function roy_b(@r int)
RETURNS @ta table(id int,parentid int)
as
begin
While Exists(Select 1 From t_tree Where id=@r and parentid<>0)
begin
insert @ta select * from t_tree where id=@r
select @r=parentid from t_tree where id=@r
end
RETURN
endselect * from dbo.roy_b(5)
效果如下:
id parentid
----------- -----------
5 2
2 1
1 -1(所影响的行数为 3 行)