SQL也能玩递归

创建表语句 ?

1 2 3 4 5 6 [sql]

CREATE TABLE [dbo].[Table_6]( [id1] [int] NOT NULL,

[name] [varchar](50) NOT NULL, [id2] [int] NOT NULL ) ON [PRIMARY]

插入数据 ?

1 2 3 4 5 6 7 8 [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中的父节点很简单，用下面的语句即可。 ?

1 2 3 4 [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的结果 ?

1 2 3 4 5 6 7 8 9 10 [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语句 ?

1 [sql]

2 WITH cte AS 3 (

4 SELECT [level]=1,id1, name, id2 from [Table_6] 5 UNION ALL

6 SELECT [level]=[level]+1, A.id1, b.name, b.id2 7 FROM cte A

8 INNER JOIN [Table_6] B 9 ON A.id2 = B.id1 10 )

11 SELECT A.id1, name = STUFF((SELECT '-'+name FROM cte B

12 WHERE b.id1=a.id1 ORDER BY [level] DESC FOR XML PATH('')),1,1,''), id2 = MAX(CAS13

FROM cte A GROUP BY id1