欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL也能玩递归

程序员文章站 2023-11-03 09:54:03
最近在做项目的时候遇到一个表,将省市区都放到一个表里存储,通过父id字段来表示省市区的关系。   创建表语句   [sql] create...
最近在做项目的时候遇到一个表,将省市区都放到一个表里存储,通过父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  

 

 

查询结果为

 

SQL也能玩递归

 

图1

 

期望的结果

 

SQL也能玩递归

 

图2

 

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

 

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

 

 

得到的结果是

 

SQL也能玩递归

 

图3

 

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

 

SQL也能玩递归

 

图4

 

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

 

SQL也能玩递归

 

图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  

 

 

得到的结果是:

 

SQL也能玩递归

 

最后如果要得到图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