SQL 客户层级查询-优快云博客

本文链接：https://blog.youkuaiyun.com/camel0564/article/details/2842195

本文介绍了一种使用 SQL 实现客户及其子级客户的层级查询方法。通过递归插入临时表的方式，逐步获取指定客户的直接及间接下属客户信息，确保所有有效客户均被纳入查询范围。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

sql 递归调用