日期:2014-05-16 浏览次数:20495 次
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
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'&