SQL递归查询

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)

----------------------

WITH ShowTree(ID) AS

(

    SELECT ID FROM dbo.Clients WHERE id=8

   

    UNION ALL

    SELECT Clients.ID FROM ShowTree AS s

    INNER JOIN Clients ON s.ID = Clients.parent_id

)

select distinct(id) from ShowTree