CTE(公用表表达式)SQL

通用表表达式(CTE)被导入到SQL中，以简化派生表的许多结构化查询语言(SQL)类，这是不合适的。它是在2005 SQL SERVER版本中引入的。

公用表表达式(CTE)是一个结果集，我们使用SELECT，INSERT，UPDATE或DELETE语句进行引用。在SQL 2008中，我们为唯一的MERGE语句添加了CTE。

如何在T-SQL中使用CTE？

Common Text Expression的使用是在SELECT，INSERT，UPDATE，DELETE或MERGE语句之前添加子句“ WITH”。 WITH子句包含一个或多个CTE，以逗号分隔。

[WITH [, ....]]
::::=
cte_name [(column_name[, ...])]__Write the name of column here
AS (cte_query)

当我们引用任何表时，我们都会生成CTE。当我们运行特定语句时，任何语句都无法访问CTE结果集。

创建递归表表达式

当我们使用分层数据时，将使用递归CTE。该表中的分层数据的一个示例是该组中的学生列表。计数器会为每个学生生成一个ReferenceID和一个NAME。 ReferenceID像递归表中的雇员ID一样引用自己。我们使用CTE来显示员工数据库的位置。

如果CTE创建错误，它将进入无限循环。

为了防止无限循环，MAXRECURSION将添加到INSERT，DELETE，UPDATE，SELECT或MERGE语句的OPTION子句中。

使用以下代码创建表：

CREATE TABLE Employees
{
EmployeeID int NOT NULL PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
LastNamevarchar(50) NOT NULL,
  Manager ID int NULL
}
INSERT INTO Employees VALUES(1, 'Ken', 'Thompson', NULL)
INSERT INTO Employees VALUES(2, 'Kent', 'Thompson', 1)  
INSERT INTO Employees VALUES(3, 'Williams', 'Thompson', 1)  
INSERT INTO Employees VALUES(4, 'Charles', 'Thompson', 2)  
INSERT INTO Employees VALUES(5, 'Michal', 'Thompson', 3)  
INSERT INTO Employees VALUES(6, 'Gill', 'Thompson', 3)  
INSERT INTO Employees VALUES(7, 'Danyl', 'Thompson', 3)  
INSERT INTO Employees VALUES(8, 'Monty', 'Thompson', 5)  
INSERT INTO Employees VALUES(9, 'Rob', 'Thompson', 6)  
INSERT INTO Employees VALUES(10, 'Robert', 'Thompson',6)

创建Employee表之后，将创建一个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.StudentID, e.FirstName, e.LastName, e.ManagerID,
r. StuLevel + 1
FROM Students p
INNER JOIN cteReports s
ON e.ManagerID = r.StuID
)
SELECT
First Name+ ' ' + LAST NAME AS FullName,StuLevel,
(SELECT FirstName + '.....' +LastName FROM Students
WHERE StudentID = cteReports.MgrID) AS ManagerFROM cteReports
ORDER BY StuLevel, MgrID

它是生成不一致结果集并在SELECT，UPDATE，INSERT，MERGE或DELETE语句中检索的重要工具。