MySQL的SQL语句 - 数据操作语句（17）- WITH 语句（1）

林员外聊编程 2020-10-07

199

WITH（公共表表达式）

公共表表达式（common table expression，CTE）是一个命名的临时结果集，存在于单个语句的范围内，定义后可以在该语句中引用它，可能多次引用。下面的讨论描述如何编写使用 CTE 的语句。

通用表表达式

若要指定公共表表达式，请使用用逗号分隔子语句的 WITH 子句。每个子句提供一个子查询，该子查询生成一个结果集，每个子查询关联一个名称。以下示例在 WITH 子句中定义名为 cte1 和 cte2 的 CTE，并在 WITH 子句后面的顶级 SELECT 中引用它们：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
复制

在包含 WITH 子句的语句中，可以引用每个 CTE 名称来访问相应的 CTE 结果集。

一个 CTE 名称可以在其他 CTE 中引用，从而可以基于其他 CTE 定义 CTE。

CTE 可以引用自身来定义递归 CTE。递归 CTE 的常见应用包括序列生成和层次或树结构数据的遍历。

公共表表达式是 DML 语句语法的可选部分。它们是使用 WITH 子句定义的：

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
复制

cte_name 命名一个公共表表达式，可以在包含 WITH 子句的语句中用作表引用。

AS (subquery) 的 subquery 部分称为“CTE 子查询”，它生成 CTE 结果集。AS 后面的括号是必需的。

如果公共表表达式的子查询引用它自己的名称，则该表达式是递归的。如果 WITH 子句中的任何 CTE 是递归的，则必须包含 RECURSIVE 关键字。

明确给定 CTE 的列名如下：

● 如果圆括号括起来的列表名称跟在 CTE 名称后面，则这些名称就是列名称：

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;
复制

列表中的名称数量必须与结果集中的列数相同。

● 否则，列名来自 AS (subquery) 部分的第一个 SELECT 的选择列表：

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;
复制

在以下上下文中允许使用 WITH 子句：

● 在 SELECT、UPDATE 和 DELETE 语句的开头。

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
复制

● 在子查询（包括派生表子查询）开始部分：

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...
复制

● 紧跟在包含 SELECT 语句的 SELECT 语句之前：

INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
复制

同一级别只允许有一个 WITH 子句。同一级别的 WITH 后面跟 WITH 是不允许的，因此以下语句是非法的：

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...
复制

要使语句合法，请使用一个 WITH 子句，用逗号分隔它的子句：

WITH cte1 AS (...), cte2 AS (...) SELECT ...
复制

但如果出现在不同的层级，一个语句可以包含多个WITH子句：

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;
复制

WITH 子句可以定义一个或多个公共表表达式，但每个 CTE 名称对于该子句必须是唯一的。以下语句是非法的：

WITH cte1 AS (...), cte1 AS (...) SELECT ...
复制

要使语句合法，请使用唯一名称定义 CTE：

WITH cte1 AS (...), cte2 AS (...) SELECT ...
复制

CTE 可以指自身或其他 CTE：

● 自引用 CTE 是递归的。

● CTE 可以引用在同一 WITH 子句中前面定义的 CTE，但不能引用后面定义的 CTE。

这个约束排除了相互递归的 CTE，其中 cte1 引用 cte2，cte2 引用 cte1。其中一个引用必须是后面定义的 CTE，这是不允许的。

● 给定查询块中的 CTE 可以引用在更外部层级的查询块中定义的 CTE，但不能引用在更内部层级的查询块中定义的 CTE。

为了解析对同名对象的引用，派生表隐藏 CTE；而 CTE 隐藏基本表、临时表和视图。名称解析的方法是在同一个查询块中搜索对象，如果找不到具有该名称的对象依次转到外部查询块。

与派生表一样，MySQL 8.0.14 之前 CTE 不能包含外部引用。这是 MySQL 8.0.14 中取消的 MySQL 限制，而不是 SQL 标准的限制。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/with.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 - 数据操作语句（17）- WITH 语句（1）

评论

相关阅读