SQL 通用表达式CTE

CTE（通用表达式）为我们提供了一个临时的结果集，必须立即与Select、Insert、Update或Delete语句一起使用，也可以在创建视图时使用。它在查询开始时定义，然后可以在外部查询中执行多次。

有两种类型的CTE

非递归CTE

一个不引用自己，也不使用递归属性的CTE。

语法

WITH cte_name (col1,col2,. . . )
AS 
(
   CTE_definition
)
SELECT * FROM cte_name;

在这里，

• CTE_definition是查询。

• 在SELECT语句的位置，我们可以使用insert、delete或update语句来进行cte。

示例1

在这个例子中，我们将看看如何在sql中创建一个cte，并了解它的实际工作原理。

步骤

• 步骤1 − 创建一个cte

• 步骤2 − 使用AS关键字

• 步骤3 − 使用select语句从输入表中提取所需的数据。

• 步骤4 − 使用select查看数据。

输入

student

代码

WITH details#cte created
AS 
(
   SELECT id,name,cgpa FROM student WHERE cgpa>8.0
   #selected the required details 
)
SELECT id,name,cgpa FROM details;#to show the result.

输出

示例2

在这个示例中，我们将创建多个CTE并将它们连接在一起以获得所需的输出。多个CTE的定义可以使用UNION、UNION ALL、JOIN、INTERSECT或EXCEPT来定义。

步骤

• 步骤1 - 创建CTE

• 步骤2 - 使用select从源表中提取数据

• 步骤3 - 创建另一个CTE

• 步骤4 - 使用select从源表中提取数据

• 步骤5 - 使用union在单个表中显示来自两个CTE的结果

输入

student

代码

WITH details#cte created
AS 
(
   SELECT id,name,cgpa FROM student WHERE cgpa>8.0
   #selected the required details 
),
table_1#another cte created
AS
(
   SELECT id,name,cgpa FROM student WHERE cgpa<7.5
   #selected the required details
)
SELECT id,name,cgpa FROM details;#to show result
UNION #used to join the cte output
SELECT id,name,cgpa FROM table_1#to show result

输出

递归CTE

递归CTE是指引用自身的CTE。它会重复执行，直到我们获得完整的结果集。

语法

WITH RECURSIVE cte_name (col1,col2,. . . )
AS 
(
   CTE_definition
)
SELECT * FROM cte_name;

这里，cte_definition是使用union all、distinct和with clause构成的递归结构。

示例

在这个示例中，我们将看到递归cte的工作原理。

步骤

• 步骤1 − 创建递归cte

• 步骤2 − 从基表中选择数据

• 步骤3 − 为选择数据提供条件

• 步骤4 − 使用union all合并数据

• 步骤5 − 使用内连接合并记录

• 步骤6 − 使用select显示输出

输入

Student_data

代码

WITH RECURSIVE specific_data(id,name,branch,sap_id,studentlevel)
#recursive cte created
AS
(
   SELECT id,name,branch,sap_id,1#data selected from base table
   FROM student_data WHERE sap_id IS NULL
   #condition is provided
   UNION ALL#used to merge the data
   SELECT s.id,s.name,s.branch,s.sap_id,d.studentlevel+1#data selected
     FROM student_data AS s
     INNER JOIN specific_data AS d
     #used to join entities
     ON s.sap_id=d.id 
)
SELECT name,branch,studentlevel FROM specific_data
#used to display output

输出

结论

CTE用于提供一个可以在select、update、insert或delete语句中引用的临时结果集。CTE可以是非递归的或递归的。非递归的CTE不引用自身。在第一个例子中，我们看到了使用WITH子句正常创建CTE的方法，在第二个例子中，我们使用union、join或intersect来处理多个CTE。第二部分是递归的CTE，它引用自身并重复执行。在这个例子中，我们使用WITH RECURSIVE子句来查看递归CTE的工作原理。