SQLserver递归查询

--建立测试表

create table #recursion(Id int,ParentId varchar(20) ,Name varchar(20))

--插入测试数据

insert into #recursion  select '1',null,'云南省' union all

select '10','1','昆明市' union all

select '20','1','曲靖市' union all

select '100','20','陆良县' union all

select '200','20','师宗县' union all

select '1000','200','召夸镇'

 

select * from#recursion

--查出表中属于曲靖市辖区的地名

with cte as 

 ( 

 select a.Id ,a.ParentId,a.Name from #recursion a where Id='20'

 union all  

 select b.Id ,b.ParentId,b.Name  from #recursion b inner join cte c on c.id = b.ParentId 

 )

select * from cte