吐血，花了两天也看不懂公共表达式(CTE)是如何完成递归的，求大牛解释

Question

我在这里
http://www.cnblogs.com/nokiaguy/archive/2009/02/01/1382207.html看到了一个CTE递归的实际应用，这个表本身是很简单CET递归如下所写with
district as 
(
    --  获得第一个结果集，并更新最终结果集
    select * from t_tree where node_name= N'辽宁省'
    union all
    --  下面的select语句首先会根据从上一个查询结果集中获得的id值来查询parent_id         
    --  字段的值，然后district就会变当前的查询结果集，并继续执行下面的select 语句
    --  如果结果集不为null，则与最终的查询结果合并，同时用合并的结果更新最终的查
    --  询结果；否则停止执行。最后district的结果集就是最终结果集。
    select a.* from t_tree a, district b
               where a.parent_id = b.id
)
select * from district
它的解释我看的云里雾里，就是没看懂，递归跳出的条件是什么。按照其它地方的解释：递归跳出的条件是定位点，也就是第一条语句
select * from t_tree where node_name= N'辽宁省'
可问题就是如果在t_tree表里搜索node_name= N'辽宁省'的话，无论递归到第几级都不可能出现空结果（t_tree表里第一行永远是搜索结果，不可能为空），递归就永远不会跳出了，所以我怎么也无法理解CTE递归是如何跳出的，有人能解释一下这个吗，感觉这个太难理解了

Answer 1

/*
标题：SQL SERVER 2005中查询指定节点及其所有子节点的方法(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2010-02-02
地点：新疆乌鲁木齐
*/create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null  , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
goDECLARE @ID VARCHAR(3)--查询ID = '001'的所有子节点
SET @ID = '001'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇(10 行受影响)
*/--查询ID = '002'的所有子节点
SET @ID = '002'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
002  001  广州市
004  002  天河区(2 行受影响)
*/--查询ID = '003'的所有子节点
SET @ID = '003'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇(7 行受影响)
*/drop table tb--注：除ID值不一样外，三个SQL语句是一样的。/*
标题：SQL SERVER 2005中查询指定节点及其所有子节点的方法(字符串形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2010-02-02
地点：新疆乌鲁木齐
*/create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null  , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
go;with t as
(
    select id , cid = id from tb 
    union all
    select t.id , cid = tb.id 
    from t join tb on tb.pid = t.cid 
)
select id , cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')
from tb
group by id
order by id
/*
id   cid
---- ---------------------------------------
001  001,002,003,005,006,007,008,009,010,004
002  002,004
003  003,005,006,007,008,009,010
004  004
005  005
006  006
007  007,008,009,010
008  008
009  009
010  010(10 行受影响)
*/;with t as
(
    select id , name , cid = id , path = cast(name as nvarchar(100)) from tb 
    union all
    select t.id , t.name , cid = tb.id , path = cast(tb.name as nvarchar(100))
    from t join tb on tb.pid = t.cid 
)
select id , name , 
       cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , ''),
       path = STUFF((SELECT ',' + path FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')
from tb
group by id , name
order by id
/*
id   name       cid                                         path
---- ---------- ------------------------------------------- ---------------------------------------------------------------------
001  广东省     001,002,003,005,006,007,008,009,010,004     广东省,广州市,深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇,天河区
002  广州市     002,004                                     广州市,天河区
003  深圳市     003,005,006,007,008,009,010                 深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇
004  天河区     004                                         天河区
005  罗湖区     005                                         罗湖区
006  福田区     006                                         福田区
007  宝安区     007,008,009,010                             宝安区,西乡镇,龙华镇,松岗镇
008  西乡镇     008                                         西乡镇
009  龙华镇     009                                         龙华镇
010  松岗镇     010                                         松岗镇(10 行受影响)
*/drop table tb

Answer 2

ls的朋友，我需要的原理解释，也就是递归的模型，你给我再多的范例，不给模型还是没弄懂CTE递归是怎么工作的

Answer 3

select a.* from t_tree a, district b
               where a.parent_id = b.id
是在这句控制跳出递归!

Answer 4

a.parent_id = b.id
相等的没有了不就跳出了吗？

Answer 5

cte是一个虚拟表,但是可以引用他自身,你注意一下cte语句里的district的引用
至于where后面的只不过是连接而已
至于你说的递归,是有条件的.需要依赖定位成员,看看这个链接
http://database.51cto.com/art/201010/229272.htm

Answer 6

我补充问一点：
1.是否不能把这个公共表达式里的语句像一般编程语言里的递归结构那样看，我想了半天，觉得递归时是只执行这句
select a.* from t_tree a, district b
  where a.parent_id = b.id
而不执行第一句，才是合理的，我猜测是否是对的？另外，第一句
select * from t_tree where node_name= N'辽宁省'
的意思是让district有一个初始的虚拟表结果，好让第二句能够引用我反复的构建了一下模型，发现只有这样才能解释的通，不知道我理解的这个模型是否适用，我之前都是按照一般编程语言里的递归结构来理解的，结果就是怎么也理解不了这段代码

Answer 7

注意 union all 语法。最后一次运行, 因为 where a.parent_id = b.id 的结果为null。 结果就是没有可联合的记录，就退出递归了。

Answer 8

递归,就是循环!
把这点弄明白就容易理解了.这句:
    select a.* from t_tree a, district b
               where a.parent_id = b.id
每循环一次,根据上次获得的b.id,与a.parent_id比较获得一个子记录,下次递归就用这个子记录的ID再找它的子记录,当找不到下级子记录时,递归也就结束了------或者说,循环也就结束了.

Answer 9

with
district as 
(
    --  获得第一个结果集，并更新最终结果集
    select * from t_tree where node_name= N'辽宁省'--定位点成员
    union all
    select a.* from t_tree a, district b
               where a.parent_id = b.id  ---递归成员,调用CTE自身与B JOIN实现递归
)
select * from district