金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 使用CTE公用表表达式的递归查询

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

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-21 10:22

公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

本文导读:with as你可以把他看做一个语句片段,也可以看做一个临时表,也叫做子查询部分。我们可以利用with as语句对查询出的结果进行二次处理,比如汇总、分类、求平均数等,实在是太方便了。

当某个查询引用递归 CTE 时,它即被称为递归查询。递归查询通常用于返回分层数据,例如:显示某个组织图中的雇员或物料清单方案(其中父级产品有一个或多个组件,而那些组件可能还有子组件,或者是其他父级产品的组件)中的数据。

一、WITH AS的含义

递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中,递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。

WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。

 

特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH AS短语,则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上,则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里,如果只是被调用一次,则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

递归 CTE 的结构

Transact-SQL 中的递归 CTE 的结构与其他编程语言中的递归例程相似。尽管其他语言中的递归例程返回标量值,但递归 CTE 可以返回多行。

递归 CTE 由下列三个元素组成:

  1. 例程的调用。

    递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集,所以它们被称为“定位点成员”。

    CTE_query_definitions金沙棋牌app手机下载, 被视为定位点成员,除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前,而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。

  2. 例程的递归调用。

    递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。

  3. 终止检查。

    终止检查是隐式的;当上一个调用中未返回行时,递归将停止。

 

二、公用表表达式(CTE),使用CTE的WITH AS语法:

伪代码和语义

递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

 

递归执行的语义如下:

  1. 将 CTE 表达式拆分为定位点成员和递归成员。

  2. 运行定位点成员,创建第一个调用或基准结果集 (T0)。

  3. 运行递归成员,将 Ti 作为输入,将 Ti+1 作为输出。

  4. 重复步骤 3,直到返回空集。

  5. 返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。

 

[ WITH <common_table_expression> [ ,n ] ]

实例

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Snchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);


USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO

 

 

<common_table_expression>::=

expression_name [ ( column_name [ ,n ] ) ]

AS

( CTE_query_definition )

三、使用CTE的SQL语句实例:

金沙棋牌app手机下载 1

三、在使用WITH AS时应注意如下几点:

1. CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。如下面的SQL语句将无法正常使用CTE:

金沙棋牌app手机下载 2

2. CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔,如下面的SQL语句所示:

金沙棋牌app手机下载 3

3. 如果CTE的表达式名称与某个数据表或视图重名,则紧跟在该CTE后面的SQL语句使用的仍然是CTE,当然,后面的SQL语句使用的就是数据表或视图了,如下面的SQL语句所示:

金沙棋牌app手机下载 4

  1. CTE 可以引用自身,也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

  2. 不能在 CTE_query_definition 中使用以下子句:

(1)COMPUTE 或 COMPUTE BY

(2)ORDER BY(除非指定了 TOP 子句)

(3)INTO

(4)带有查询提示的 OPTION 子句

(5)FOR XML

(6)FOR BROWSE

  1. 如果将 CTE 用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾,如下面的SQL所示:

金沙棋牌app手机下载 5

四、使用with as实现递归调用

实现递归的CTE语法如下:

金沙棋牌app手机下载 6

**with as实现递归 的SQL语句:**

金沙棋牌app手机下载 7

五、在定义和使用递归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 的递归引用的提示。

  1. 无论参与的 SELECT 语句返回的列的为空性如何,递归 CTE 返回的全部列都可以为空。

  2. 如果递归 CTE 组合不正确,可能会导致无限循环。例如,如果递归成员查询定义对父列和子列返回相同的值,则会造成无限循环。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值,来限制特定语句所允许的递归级数,以防止出现无限循环。这样就能够在解决产生循环的代码问题之前控制语句的执行。服务器范围内的默认值是 100。如果指定 0,则没有限制。每一个语句只能指定一个 MAXRECURSION 值。

  3. 不能使用包含递归公用表表达式的视图来更新数据。

  4. 可以使用 CTE 在查询上定义游标。递归 CTE 只允许使用快速只进游标和静态(快照)游标。如果在递归 CTE 中指定了其他游标类型,则该类型将转换为静态游标类型。

  5. 可以在 CTE 中引用远程服务器中的表。如果在 CTE 的递归成员中引用了远程服务器,那么将为每个远程表创建一个假脱机,这样就可以在本地反复访问这些表。

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:使用CTE公用表表达式的递归查询

关键词:

上一篇:没有了

下一篇:没有了