sql 递归调用

Id

Name

Parent_id

Audit_start

Audit_end

1

Client1

<null>

2008.08.02

<null>

2

Division1

1

2008.08.02

<null>

3

Division2

2

2008.08.02

<null>

----------------------------------------------------------------------
declare @clientId int
set @clientId = 1

-----------------------------------------------------------------
--get all subclients belong to this client including itself.
declare @tbl_clients table
(
    client_id int,
    client_name varchar(100),
    level int
)
----------------------------------------------------------
--insert itself
insert into @tbl_clients (client_id,client_name,level)
select @clientId,name,0
from clients
where id= @clientId and audit_end is null

--insert its children
declare @level int
  set @level=1

insert into @tbl_clients
  select id,name,@level from clients where parent_id=@clientId and audit_end is null

  while @@rowcount>0
    begin
        set @level=@level+1
      insert into @tbl_clients
      select clients.id, clients.name,@level
      from clients,@tbl_clients tmp
      where clients.parent_id=tmp.client_id
      and tmp.level=@level-1
        and clients.audit_end is null
    end

select * from @tbl_clients

select * from clients where audit_end is null