SQL 通用表达式

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

通用表达式（CTE）是一个结果集，我们可以在SELECT、INSERT、UPDATE或DELETE语句中引用它们。在SQL 2008中，我们为唯一的MERGE语句添加了一个CTE。

如何在T-SQL中使用CTEs？

使用通用文本表达式的方法是在SELECT、INSERT、UPDATE、DELETE或MERGE语句之前添加”WITH”子句。WITH子句包含一个或多个CTE，它们用逗号分隔。

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

当我们引用任何表时，我们会生成CTEs。CTEs结果集在运行特定语句时对任何语句都不可访问。

创建递归表达式

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

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

为了避免无限循环，在INSERT、DELETE、UPDATE、SELECT或MERGE语句的OPTION子句中添加MAXRECURSION。

使用以下代码创建一个表：

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语句，该语句之前有一个包含名为cteReports的CTE的WITH子句：

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 语句中检索。