SQL Server CTE

SQL Server CTE，也称为公用表表达式，用于生成一个临时命名集（类似于临时表），该集在查询期间存在。我们可以在单个 SELECT、INSERT、DELETE 或 UPDATE 语句的执行范围内定义此 CTE。使用它的基本规则是：

  1. SQL Server CTE 必须紧随一个 SELECT、INSERT、DELETE 或 UPDATE 语句，该语句使用部分或全部公用表表达式列。
  2. 我们可以使用 UNION、UNION ALL、INTERSECT 或 EXCEPT 定义多个定义。
  3. 它可以引用自身和以前定义的公用表表达式，但不能引用下一个（前向引用）。
  4. 在公用表表达式中，我们不能引用远程服务器上的表。
  5. 在定义中，您不能使用以下子句：
    1. 除非您使用 TOP 子句，否则不能使用 ORDER BY 子句。
    2. 带有查询提示的 INTO、FOR BROWSE 和 OPTION 子句。

SQL Server CTE 语法

SQL Server 公用表表达式或 CTE 的语法是：

WITH Expression_Name (Column_Name1, ColumnName2,.....ColumnNameN)
AS
(Define) -- Write a query

SELECT Column_Name1, ColumnName2,.....ColumnNameN
FROM Expression_Name -- Or, Name

公用表表达式的参数是：

  • Expression_Name：请为您要创建的公用表表达式指定一个有效且唯一的名称。它必须与同一 WITH 中定义的任何其他名称不同。
  • Column_Name1, ColumnName2,.....ColumnNameN：请指定有效且唯一的列名。因为 SQL Server 不允许任何重复的名称。在这里，您指定的列数应与定义的结构匹配。
  • 定义：为此编写您的查询。

我们使用 [Employee 表] 和 [Department] 表进行此 SQL Server CTE 公用表表达式演示。

[Department] 表有八条记录。

SQL CTE 公用表表达式示例

这个简单的示例将向您展示如何编写一个简单的 CTE。

WITH Total_Sale 
AS
(
SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) AS Income
      ,SUM([Sales]) AS Sale
  FROM [Employee Table]
  GROUP BY [Education], [Occupation]
)
SELECT * FROM Total_Sale
Simple SQL Server Common Table Expression CTE Example 2

在此 CTE 中，我们通过对职业和教育进行分组，对数字列（年收入和销售额）执行聚合（SUM）。因此，我建议您参考 GROUP BY 子句文章。

SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) AS Income
      ,SUM([Sales]) AS Sale
  FROM [Employee Table]
  GROUP BY [Education], [Occupation]

接下来，我们使用 SELECT 语句选择 Total_Sale 返回的所有记录。

SELECT * FROM Total_Sale

SQL Server CTE 公用表表达式列名示例

始终建议为 CTE 或公用表表达式添加列名。因为即使您在定义中使用了重复的名称或未给列命名，它也不会抛出任何错误。在这里，我们为公用表表达式 CTE 添加列名。

WITH Total_Sale (Profession, Qualification, Income, Sale)
AS
(
SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) -- No Alias Column Name
      ,SUM([Sales]) -- No Alias Column Name
  FROM [Employee Table]
  GROUP BY [Education], [Occupation]
)
SELECT * FROM Total_Sale
Common Table expressions Column Names Example 3

如果您不使用列名，那么以下两个语句将抛出错误，因为它们没有别名列名。因此，它会将 Income 和 Sale 分配为它们的列名。

  ,SUM([YearlyIncome]) -- No Alias Column Name
  ,SUM([Sales]) -- No Alias Column Name

如何选择每个组的第一行？

让我们看看如何选择每个组的第一行。

WITH Highest_Record AS
(
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (PARTITION BY [Occupation] 
                          ORDER BY [YearlyIncome] DESC) AS Number
      ,[Sales]
      ,[HireDate]
  FROM [Employee Table]
  )
-- It will select all the records whose Rank is 1 (Highest rank)
SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM Highest_Record AS hr
-- If you omit this where clause, then all the records are returned.
WHERE hr.Number = 1
CTE Select First Row in Each Group Example 4

上述 SQL 公用表表达式中的以下语句将为 Employee 表中的每一行分配唯一的行号。请参阅 ROW_NUMBER 和 ORDER BY 子句以理解以下查询。

 ,ROW_NUMBER() OVER (PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC) AS Number

SQL Server 中多个 CTE 示例

在此示例中，我们向您展示如何在 WITH 语句的单个查询中使用多个 CTE 查询定义或表达式。如前所述，我们可以在一个 WITH 语句中编写多个公用表表达式或 CTE 语句，并通过逗号分隔它们。

WITH Highest_Record AS
(
SELECT Id
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (PARTITION BY [Occupation] 
                          ORDER BY [YearlyIncome] DESC) AS Number
      ,[Sales]
      ,[HireDate]
  FROM [Employee Table]
),
-- Use Comma to Separate multiple expressions - Highest_Record and Department_Name
Department_Names AS
(
SELECT *
  FROM [Department]
)
-- We are using LEFT Join to join 
SELECT hr.[FirstName] + ' ' + hr.[LastName] AS [Full Name]
      ,hr.[Education]
      ,hr.[Occupation]
      ,dept.DepartmentName
      ,hr.[YearlyIncome]
      ,hr.[Sales]
      ,hr.[HireDate]
      ,hr.Number AS [Rank]
FROM Highest_Record AS hr
LEFT JOIN 
    Department_Names AS dept ON
	hr.Id = dept.id

提示：请参阅 LEFT JOIN。

Multiple CTE Example 5

SQL Server CTE 递归示例

以下是定义递归 CTE 的基本准则：

  • 递归公用表表达式必须至少包含两个查询定义：一个递归成员和一个锚点成员。
  • 您可以使用任何集合运算符：UNION、UNION ALL、EXCEPT 或 INTERSECT 来组合 SQL 递归公用表表达式的锚点成员。但是，UNION ALL 运算符是唯一用于连接多个递归成员的运算符。
  • 锚点成员和递归成员中的列数和数据类型应相同。
  • 您不能在定义的递归成员内部使用 GROUP BY、SELECT DISTINCT、PIVOT、HAVING、TOP、LEFT JOIN、RIGHT JOIN、OUTER JOIN、子查询和标量聚合。

此示例展示了如何编写递归 CTE。我们使用 [Employee table] 表进行此递归公用表表达式演示。

Source Table

递归公用表表达式 CTE 查询返回以下结果。

WITH Recursion AS   
(  
    SELECT [EmployeeID],[FirstName],[LastName],[Education],[Occupation]
			,[YearlyIncome],[Sales],[ManagerID],1 AS EmployeeLevel  
    FROM MyEmployees   
    WHERE ManagerID IS NULL  
    UNION ALL  
	SELECT emp.[EmployeeID],emp.[FirstName],emp.[LastName],emp.[Education]
      ,emp.[Occupation],emp.[YearlyIncome],emp.[Sales],emp.[ManagerID]
      ,EmployeeLevel + 1  
    FROM MyEmployees AS emp  
    INNER JOIN Recursion AS rec  
        ON emp.ManagerID = rec.EmployeeID   
)  
SELECT * FROM Recursion  
ORDER BY EmployeeLevel, ManagerID;
Recursive CTE Example 8

递归显示分层列表

在这里，我们还通过修改上述 SQL Server 示例来使用递归 CTE 公用表表达式查询定义，以可视化地显示分层级别。

WITH Recursion
AS 
(
	SELECT  EmployeeID, 1 AS [Designation Level],
			CONVERT(varchar(255), FirstName + ' ' + LastName) AS Name,[DeptID]   
	FROM MyEmployees
	WHERE ManagerID IS NULL  
	UNION ALL  
	SELECT emp.EmployeeID, [Designation Level] + 1,
      CONVERT(varchar(255), REPLICATE ('|    ' , [Designation Level]) +  
                                        emp.FirstName + ' ' + emp.LastName),
      emp.[DeptID]     
	FROM MyEmployees AS emp  
	JOIN Recursion AS rec ON emp.ManagerID = rec.EmployeeID  
)  
SELECT rec.EmployeeID, rec.[Designation Level], rec.Name, dept.DepartmentName  
FROM Recursion AS rec
INNER JOIN Department AS dept ON  rec.DeptID = dept.id
ORDER BY [Designation Level]
SQL Server CTE Recursive to display Hierarchical List 9