引言
今天学员群有位亲遇到这样一个问题:父子级省市数据希望能够拉平成省、市、县、镇。父子级这种问题,解决之道通常就是递归。之前可能需要游标或者嵌套循环来解决,现在MSSQL提供了CTE这个强大的东东,支持递归查询。万民之福啊!!!
知识点
公用表表达式(Common Table Expression) CTE:是SQL SERVER 2005版本之后引入的一个特性.CTE可以看作是一个临时的结果集,可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练.
递归公用表表达式很像派生表(Derived Tables ):,指的是在CTE内的语句中调用其自身的CTE.与派生表不同的是,CTE可以在一次定义多次进行派生递归.对于递归的概念,是指一个函数或是过程直接或者间接的调用其自身。
案例
step1、测试数据(群友友情赞助,赞个。在这儿也提下,提问SQL这种问题的时候最好都提供下示例数据,要不自己造只想吐槽)
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' , '松岗镇');
step2、解决方案
with tmp
as
(
select id,pid,name,1 tlevel,cast(id as varchar(4000)) as tcode from tb where tb.pid is null
union all
select tb.id,tb.pid,tb.name,tlevel+1,cast(tmp.tcode+tb.id as varchar(4000)) from tb
join tmp on tb.pid = tmp.id
)
,tmp1
as
(
select *
from tmp where id not in(
select pid from tmp where pid is not null
)
)
select max(case when tmp.tlevel = 1 then tmp.name end) 省,
max(case when tmp.tlevel = 2 then tmp.name end) 市,
max(case when tmp.tlevel = 3 then tmp.name end) 县,
max(case when tmp1.tlevel = 4 then tmp1.name else null end) 镇
from tmp1
left join tmp on tmp1.tcode like tmp.tcode+'%'
group by tmp1.tcode
step3、效果展示
其中的概念和思路可以参考下面的博客。
参考链接:http://www.cnblogs.com/zjflove/p/4245990.html
扩展
题目中提到了无限级这个高大上的词,实际案例中却是限定级的,楼主这个标题党。各位亲是不是已经在心里无数次的问候过我了。莫急,我觉得按上面的思路实现无限级仅是时间问题。各位可以顺着这个思路尝试下……