sql 递归调用

最新推荐文章于 2025-04-10 16:33:25 发布

原创最新推荐文章于 2025-04-10 16:33:25 发布 · 820 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #null #insert #table

SQL Server 专栏收录该内容

27 篇文章

订阅专栏

本文介绍了一种使用 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