SQL中的CTE

这个 通用表表达式（CTE） 为了简化派生表不适合的各类SQL查询，在标准SQL中引入了。CTE是在SQL Server 2005中引入的，公共表表达式（CTE）是一个临时命名的结果集，可以在SELECT、INSERT、UPDATE或DELETE语句中引用。还可以在创建视图中使用CTE，作为视图的选择查询的一部分。此外，从SQLServer2008开始，您可以向新的MERGE语句中添加CTE。

使用CTE—— 我们可以通过在SELECT、INSERT、UPDATE、DELETE或MERGE语句之前直接添加WITH子句来定义CTE。WITH子句可以包括一个或多个用逗号分隔的CTE。可以遵循以下语法：

[WITH  [, ...]]   ::=cte_name [(column_name [, ...])]AS (cte_query) 

在用CTE定义WITH子句后，可以像引用任何其他表一样引用CTE。但是，只能在紧跟WITH子句之后的语句的执行范围内引用CTE。运行语句后，CTE结果集对其他语句不可用。

创建递归公共表表达式- 递归CTE是在该CTE中引用自身的CTE。递归CTE在处理分层数据时非常有用，因为CTE将继续执行，直到查询返回整个分层。

分层数据的一个典型示例是包含员工列表的表。对于每位员工，该表提供了该员工经理的参考。该引用本身就是同一个表中的员工ID。您可以使用递归CTE来显示员工数据的层次结构。

如果CTE创建不正确，它可能会进入无限循环。为了防止出现这种情况，可以在主SELECT、INSERT、UPDATE、DELETE或MERGE语句的OPTION子句中添加MAXRECURSION提示。

将创建一个表：

CREATE TABLE Employees(  EmployeeID int NOT NULL PRIMARY KEY,  FirstName varchar(50) NOT NULL,  LastName varchar(50) NOT NULL,  ManagerID int NULL)INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL)INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1)INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1)INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2)INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2)INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3)INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3)INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5)INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6)INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6) 

创建Employees表后，将创建以下SELECT语句，该语句前面有一个WITH子句，其中包含一个名为cteReports的CTE：

WITH  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)  AS  (    SELECT EmployeeID, FirstName, LastName, ManagerID, 1    FROM Employees    WHERE ManagerID IS NULL    UNION ALL    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,       r.EmpLevel + 1    FROM Employees e      INNER JOIN cteReports r        ON e.ManagerID = r.EmpID  )SELECT  FirstName + ' ' + LastName AS FullName,   EmpLevel,  (SELECT FirstName + ' ' + LastName FROM Employees     WHERE EmployeeID = cteReports.MgrID) AS ManagerFROM cteReports ORDER BY EmpLevel, MgrID 

因此，当您需要生成可以在SELECT、INSERT、UPDATE、DELETE或MERGE语句中访问的临时结果集时，CTE是一个有用的工具。