公用表表达式with.docxVIP

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。递归 CTE 的结构递归 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.?????? 终止检查。 终止检查是隐式的；当上一个调用中未返回行时，递归将停止。 注意：如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。在测试递归查询的结果时，可以通过在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之间的值，来限制特定语句允许的递归级数。伪代码和语义递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。 WITH cte_name(column_name [,...n] )AS(CTE_query_definition-- Anchor member is defined.UNION ALLCTE_query_definition-- Recursive member is defined referencing cte_name.)-- Statement using the CTESELECT *FROM cte_name递归执行的语义如下：1.?????? 将 CTE 表达式拆分为定位点成员和递归成员。2.?????? 运行定位点成员，创建第一个调用或基准结果集 (T0)。3.?????? 运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。4.?????? 重复步骤 3，直到返回空集。5.?????? 返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。下面举两个例子，例子1：CREATE TABLE Test(Name VARCHAR(50),ParentnameVARCHAR(50));INSERT INTO Testvalues(仙桃,湖北),(湖北,中国)INSERT INTO Test(name) values(中国)需求：查询一个名称的所有，上级名称。如仙桃，则是 中国湖北仙桃，湖北，则是 中国湖北。with city(str,name,Parentname)as(select name ,name,Parentname from test where name =仙桃??? UNION ALL???? ??? select a.str||b.name,b.name,b.Parentname from city? a,testb??? where a.Parentname=b.name)select * from city?可以看到，结果集是递归程序每一步的结果的并集。我们需要的结果是第三个。可以改一下select语句select * from city where partentname is null例子2：CREATE TABLE Test(User_NameVARCHAR(12),CityVARCHAR(12));INSERT INTO Test(User_Name,City)values (张三,杭州),(张三,郑州),(李四,杭州),(张三,南昌),(李四,广州),(王五,北京);表如下：?需求：查找每个人到过的所有城市。这相当于一个字符串分组连接的例子。可以利用双重FOR循环来解决。第一个FOR，循环名字，第二个FOR，根据名字来循环城市。也可以利用with语句来实现。这里使用两个with，第一个的记录如下：?我们只要再构建一个