CTE 递归应用实例

create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')



create function GetSonBom(@id nvarchar(10))
returns @ts table(id nvarchar(10),pid nvarchar(10),name nvarchar(20))
as
begin
with test(id,pid,name,level)
as
(
    select id,pid,name,0 as level from tb  where id=@id
    union all
    select a.id,a.pid,a.name ,level+1
    from tb a inner join test b on a.pid=b.id
)
insert into @ts select id,pid,name from test order by level
return
end



select * from GetSonBom(N'001')