SQL也能玩递归

最近在做项目的时候遇到一个表，将省市区都放到一个表里存储，通过父id字段来表示省市区的关系。

 

创建表语句

 

[sql] 
create table [dbo].[table_6](  
    [id1] [int] not null,  
    [name] [varchar](50) not null,  
    [id2] [int] not null  
) on [primary]  

 

 

插入数据

 

[sql] 
insert table_6   
select 1,'江苏',0 union all   
select 2,'南京',1 union all  
select 3,'杭州',4 union all  
select 4,'浙江',0 union all  
select 5,'白下',2 union all  
select 6,'余杭',3  

 

 

查询结果为

 

 

图1

 

期望的结果

 

 

图2

 

如果该记录的id2能够在表内找到，则将id2对应的记录的名称与当前记录的名称拼接在一起，如果其上层记录的id2不为0，就继续往上找，直到找到其id2为0为止。要找到图1中的父节点很简单，用下面的语句即可。

 

[sql] 
select * from [table_6] a  
inner join [table_6] b       
on a.id2=b.id1  

 

 

得到的结果是

 

 

图3

 

这里如果我们仅取图3中的第一列，第五列和最后一列

 

 

图4

 

将图1和图4一起看，会发现现在已经取得了图1中的每个记录的父记录，并且id1都是第一层记录的id。图4中只有第2和3条记录的id2不为0，也就是说其还有父记录，如果将图4的结果集再和图1关联一次，那么就得到了下面的结果

 

 

图5

 

将图1、图4和图5合并起来看，然后按id1分组，就会发现将每个组中的name拼接起来就是期望的图2的结果了。上述的方法对于有限级有用，但是如果是无限级拼接又该怎么办呢。

 

在sql server 2005中提供了公用表表达式(cte)，这个类似于表变量，但是比表变量效率高，通过它可以实现递归访问表的效果，例如要一次得到图1、图4和图5的结果

 

[sql] 
with cte as   
(   
    select [level]=1,id1, name, id2 from [table_6]      
    union all      
    select [level]=[level]+1, a.id1, b.name, b.id2       
    from cte a    
    inner join [table_6] b               
    on a.id2 = b.id1   
) select * from cte order by id1  

 

 

得到的结果是：

 

 

最后如果要得到图2的结果，给出完整的sql语句

 

[sql] 
with cte as   
(   
    select [level]=1,id1, name, id2 from [table_6]      
    union all      
    select [level]=[level]+1, a.id1, b.name, b.id2       
    from cte a    
    inner join [table_6] b               
    on a.id2 = b.id1   
)   
select a.id1, name = stuff((select '-'+name from cte b   
where b.id1=a.id1 order by [level] desc for xml path('')),1,1,''), id2 = max(case when [level]=1 then id2 end)   
from cte a group by id1