你的位置:首页 > 数据库

[数据库]简单自关联表中,查找所有下级数据的函数


if exists (select * from sys.objects where name='func_all_related_levels')  drop function func_all_related_levelsgocreate function func_all_related_levels (  @id int)returns @result table (  id int)asbegin  ------第一次插入数据。  declare @total int;  select @total = count(*) from TestTable where PId = @id  if @total = 0    return;  insert @result    select Id from TestTable where PId = @id;  ------遍历,可用。  declare @cursor int = 0;  while @cursor < @total    begin      declare @nextId int;      select @nextId = id from @result order by id offset (@cursor) rows fetch next 1 rows only;      insert @result        select id from dbo.func_all_related_levels(@nextId);      set @cursor = @cursor + 1;    end  ------游标,出现多余数据。  --declare c cursor for select id from @result;  --open c;  --while @@FETCH_STATUS = 0  --  begin  --    declare @nextId int;  --    fetch next from c into @nextId;  --    insert @result  --      select id from dbo.func_all_related_levels(@nextId);  --  end  --close c;  --deallocate c;  return;end