SQL Server中with as递归

先看如下一个数据表（t_tree）：

    上图显示了一个表中的数据，这个表有三个字段：id、node_name、parent_id。实际上，这个表中保存了一个树型结构，分三层：省、市、区。其中id表示当前省、市或区的id号、node_name表示名称、parent_id表示节点的父节点的id。
    现在有一个需求，要查询出某个省下面的所有市和区（查询结果包含省）。如果只使用SQL语句来实现，需要使用到游标、临时表等技术。但在SQL Server2005中还可以使用CTE来实现。

    从这个需求来看属于递归调用，也就是说先查出满足调价的省的记录，在本例子中的要查“辽宁省”的记录，如下：

id   node_name   parent_id

1     辽宁省        0

    然后再查所有parent_id字段值为1的记录，如下：

id   node_name   parent_id

2      沈阳市       1

3      大连市       1

    最后再查parent_id字段值为2或3的记录，如下：

id    node_name    parent_id

4       大东区        2

5       沈河区        2

6       铁西区        2

    将上面三个结果集合并起来就是最终结果集。

    上述的查询过程也可以按递归的过程进行理解，即先查指定的省的记录（辽宁省），得到这条记录后，就有了相应的id值，然后就进入了的递归过程，如下图所示。

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

 

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

),

district1 as

(

  select a.* from district a where a.id in (select parent_id from district) 

)

select * from district1