SQL Server中with as使用介绍

[ WITH <common_table_expression> [ ,n ] ]<common_table_expression>::=
         expression_name [ ( column_name [ ,n ] ) ]    AS (
        CTE_query_definition1  —   定位点成员（也就是初始值或第一个结果集）
       union all
        CTE_query_definition2  —   递归成员
     )

sql语句

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 

    注：只有“辽宁省”和“沈阳市”有下子节点。

    在定义和使用递归CTE时应注意如下几点：

1. 递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。
2. 定位点成员必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 集合运算符。
3. 定位点成员和递归成员中的列数必须一致。
4. 递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。
5. 递归成员的 FROM 子句只能引用一次 CTE expression_name。
6. 在递归成员的 CTE_query_definition 中不允许出现下列项：

（1）SELECT DISTINCT
（2）GROUP BY
（3）HAVING
（4）标量聚合
（5）TOP
（6）LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）
（7）子查询
（8）应用于对 CTE_query_definition 中的 CTE 的递归引用的提示。