数据表中tbCustomer存在cId,cName,cParentId三个字段，表示客户的层级关系，其中tParentId和tId关联。请编写SQL获取指定索引

[xiongxyt2]

数据表中tbCustomer存在cId,cName,cParentId三个字段，表示客户的层级关系，其中tParentId和tId关联。请编写SQL获取指定索引的所有下级客户。
declare @tId int;

[LongRui888]

试试这个：

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
*/

[--小F--]

;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

[LongRui888]

也可以参考一下这个： 在论坛中出现的比较难的sql问题：21(递归问题3) http://blog.csdn.net/sqlserverdiscovery/article/details/18363633

[xiongxyt2]

tid是另外一个表的

[LongRui888]

可以先设置一个变量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
*/

[xxfvba]

declare @tid int set @tid=1 ;with customer(cld,cName,cParentId) as (select 1,'A',0 union all select 2,'B',1 union all select 3,'C' ,1 union all select 4,'D',2), t as (select * from customer where cParentId=@tId union all select a.cld,a.cName,t.cParentId from customer a,t where t.cld=a.cParentId) select * from customer where cld=@tid union all select * from t where cParentId=@tid