数据表中tbCustomer存在cId,cName,cParentId三个字段，表示客户的层级关系，其中tParentId和tId关联。请编写SQL获取指定索引的所有下级客户。
declare @tId int;
------解决方案--------------------
试试这个：
create table tbCustomer(
cId int, --节点id
cParentId int, --父节点id
cName varchar(50) --部门名称
)
insert into tbCustomer
select 1 ,0 ,'AA' union all
select 20 ,1 ,'BB' union all
select 64 ,20 ,'CC' union all
select 22 , 1 ,'DD' union all
select 23 , 22 ,'EE' union all
select 24 , 1 ,'FF' union all
select 25 , 0 ,'GG' union all
select 26 , 1 ,'HH' union all
select 27 , 25 ,'II'
go
;with t
as
(
select cId,cParentId,cName
from tbCustomer
where cName = 'AA'
union all
select t1.cId,t1.cParentId,t1.cName
from t
inner join tbCustomer t1
on t.cid = t1.cParentId
)
select *
from t
where not exists(select 1 from tbCustomer t1 where t1.cParentId = t.cid)
/*
cId cParentId cName
24 1 FF
26 1 HH
23 22 EE
64 20 CC
*/
------解决方案--------------------
;with f as
(
select * from tbCustomer as a where cId='xxx'
union all
select a.* from tbCustomer as a inner join f as b on a.cid=b.cParentId
)
select * from f
------解决方案--------------------
可以先设置一个变量declare @tId int;：
create table tbCustomer(
cId int, --节点id
cParentId int, --父节点id
cName varchar(50) --部门名称
)
insert into tbCustomer
select 1 ,0 ,'AA' union all
select 20 ,1 ,'BB' union all
select 64 ,20 ,'CC' union all
select 22 , 1 ,'DD' union all
select 23 , 22 ,'EE' union all
select 24 , 1 ,'FF' union all
select 25 , 0 ,'GG' union all
select 26 , 1 ,'HH' union all
select 27 , 25 ,'II'
go
declare @tId int;
set @tId = 1
;with t
as
(
select cId,cParentId,cName
from tbCustomer
where cid = @tid
union all
select t1.cId,t1.cParentId,t1.cName
from t
inner join tbCustomer t1
on t.cid = t1.cParentId
)
select *
from t
where not exists(select 1 from tbCustomer t1 where t1.cParentId = t.cid)
/*
cId cParentId cName
24 1 FF
26 1 HH
23 22 EE
64 20 CC
*/