SQL递归查询

declare  @t table(RecID int,PUnitNo nvarchar(100),UnitNo nvarchar(100),PathDepth int,Path nvarchar(100))
declare @level int
set @level=1

insert into @t
SELECT RecID,PUnitNo,UnitNo,@level AS PathDepth,Path
FROM RptSystemTree WHERE PUnitNo=''

WHILE @@rowcount>0
BEGIN
    SET @level=@level+1
    INSERT INTO @t
    select a.RecID,a.PUnitNo,a.UnitNo,@level,(CASE WHEN b.Path='' THEN a.PUnitNo ELSE  b.Path+'.'+a.PUnitNo END)
    from RptSystemTree a,@t b
    where a.PUnitNo=b.UnitNo
    and b.PathDepth=@level-1
END

UPDATE RptSystemTree SET RptSystemTree.PathDepth = t.PathDepth,
RptSystemTree.Path = t.Path
FROM @t t
WHERE t.RecID = RptSystemTree.RecID

-------------------------------------
create table t
(bid int,   p_bid      int)
insert into t
select 1,         null  union all
select 2,           1   union all
select 3,           1   union all
select 4,           3   union all
select 5,           4   union all
select 6,           5   union all
select 7,           1   union all
select 8,           2

create function dbo.aa(@parent int)
returns @t table(p_bid int,bid int,level int)
as
begin
  declare @level int
  set @level=1
  insert into @t
  select p_bid,bid,@level from t where p_bid=@parent
  while @@rowcount>0
    begin
      set @level=@level+1
      insert into @t
      select a.p_bid,a.bid,@level
      from t a,@t b
      where a.p_bid=b.bid
      and b.level=@level-1
    end
  return
end
GO
select space(level*5)+cast(bid as varchar),* from dbo.aa(1)