使用公用表表达式的递归查询(CTE)

优点：
公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
在 SQL Server 2005 中，当某个查询引用递归 CTE 时，它即被称为“递归查询”。递归查询通常用于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组件）中的数据。
递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中，递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。有关公用表表达式的详细信息，请参阅使用公用表表达式。

 递归 CTE 的结构：
1、例程的调用。递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为“定位点成员”。CTE_query_definitions 被视为定位点成员，除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。
2、例程的递归调用。递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。
3、终止检查。终止检查是隐式的；当上一个调用中未返回行时，递归将停止。

递归执行的语义如下：
1、将 CTE 表达式拆分为定位点成员和递归成员。
2、运行定位点成员，创建第一个调用或基准结果集 (T0)。
3、运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。
4、重复步骤 3，直到返回空集。
5、返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。

示例代码(获取包括自身的所有父级数据)：

with get_dim_Region_id as

(

select * from dim_Region where id = 962 union all select a.* from dim_Region a join get_dim_Region_id b on a.id = b.parentId )

)

select * from get_dim_Region_id

 

结果如下：