一篇文章了解 SQL 中的 CTE

前言

本文隶属于专栏《大数据技术体系》，该专栏为笔者原创，引用请注明来源，不足和错误之处请在评论区帮忙指出，谢谢！

本专栏目录结构和参考文献请见大数据技术体系

---

WHAT

Common Table Expressions（CTE）被引入标准SQL，以简化各种不适合派生表的 SQL 查询。

CTE 是在 SQL Server 2005 中引入的，通用表表达式（CTE）是一个临时命名的结果集，我们可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中引用。

我们还可以在创建一个视图中使用 CTE，作为视图 SELECT 查询的一部分。

此外，从 SQL Server 2008 开始，可以将 CTE 添加到新的 MERGE 语句中。

---

语法

CTE 查询以WITH开头，后跟表达式名称。

语法规则如下所示：

WITH expression_name [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )

我们可以通过直接在 SELECT、INSERT、UPDATE、DELETE 或 MERGE 语句之前添加 WITH 子句来定义CTE。

WITH 子句可以包括一个或多个用逗号分隔的 CTE。

---

使用

在使用 CTE 定义 WITH 子句后，可以引用 CTE，就像引用任何其他表一样。

要查看 CTE 结果，我们使用带有 CTE 表达式名称的 SELECT 查询。

SELECT [Column1,Column2,Column3 …..] FROM expression_name

或者

Select * from expression_name

但是，只能在紧随 WITH 子句之后的语句的执行范围内引用 CTE。

运行语句后，CTE 结果集对于其他语句就不可用了。

---

类型

CTE 有两种类型：递归和非递归

---

非递归 CTE

非递归 CTE 很简单，CTE 不使用任何递归或者是子流程的重复处理。

我们将创建一个简单的非递归 CTE，以显示从 1 到 10 的行号。

根据 CTE 语法，每个CTE查询将以WITH开头，后跟带有字段列表的 CTE 表达式名称。

在这里，我们只使用一列作为ROWNO。

接下来是查询部分，在这里，我们编写要为我们的 CTE 执行的 SELECT 查询。

创建我们的 CTE 查询来运行 CTE 之后，使用带有 CTE 表达式名称的 SELECT 语句。

WITH ROWCTE(ROWNO) AS  
   (  
     SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM sys.databases 
WHERE database_id <= 10
    )
 
SELECT * FROM ROWCTE

当我们运行查询时，我们可以看到下面的输出：

---

递归 CTE

递归 CTE 的意思是 在该 CTE 中引用自己。

递归查询会调用自己直到查询满足终止条件。

接下来我们将创建一个简单的递归查询，使用 CTE 显示从 1 到 10 的行号。

首先，我们将整数变量声明为 RowNo，并将默认值设置为 1，并且我们创建了第一个 CTE 查询作为表达式名称ROWCTE。

在我们的 CTE 中，我们将首先显示默认行号，然后使用UNION ALL逐一增加和显示行号，直到行号达到增量值 10。

为了查看结果，我们将使用 SELECT 查询来显示我们的 CTE 结果。

Declare @RowNo int =1;
;with ROWCTE as  
   (  
      SELECT @RowNo as ROWNO    
		UNION ALL  
      SELECT  ROWNO+1  
  FROM  ROWCTE  
  WHERE RowNo < 10
    )  
 
SELECT * FROM ROWCTE 

当我们运行查询时，我们可以看到以下输出：

---

关键

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