MySQL CTE通用表表达式的用法（非常详细）

MySQL 中的通用表表达式简称为 CTE（Common Table Expressions）。CTE 是命名的临时结果集，作用范围是当前语句。

CTE 可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE 可以引用其他 CTE，但子查询不能引用其他子查询。

CTE 的语法格式如下：

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

使用 WITH 语句创建 CTE 的情况如下：

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 ...

下面通过示例来讲解通用表表达式的使用方法。

【实例】创建商品表 goods，该数据表包含上下级关系的数据，具体字段包含商品编号（id）、商品名称（name）、上级商品的编号（gid）。创建语句如下：

CREATE TABLE goods(?
id int(11),
name varchar(30),
gid int(11),
PRIMARY KEY (`id`));

插入演示数据：

INSERT INTO goods (id, name, gid) VALUES (1, '商品',0);
INSERT INTO goods (id, name, gid) VALUES (2, '水果',1);
INSERT INTO goods (id, name, gid) VALUES (3, '蔬菜',1);
INSERT INTO goods (id, name, gid) VALUES (4, '苹果',2);
INSERT INTO goods (id, name, gid) VALUES (5, '香蕉',2);
INSERT INTO goods (id, name, gid) VALUES (6, '菠菜',3);
INSERT INTO goods (id, name, gid) VALUES (7, '萝卜',3);

下面开始查询每个商品对应的上级商品名称。这里使用子查询的方式：

mysql> SELECT g.*, (SELECT name FROM goods where id = g.gid) as pname FROM goods AS g;
+----+------+-----+-------+
| id | name | gid | pname |
+----+------+-----+-------+
|  1 | 商品 |    0 | NULL |
|  2 | 水果 |    1 | 商品 |
|  3 | 蔬菜 |    1 | 商品 |
|  4 | 苹果 |    2 | 水果 |
|  5 | 香蕉 |    2 | 水果 |
|  6 | 菠菜 |    3 | 蔬菜 |
|  7 | 萝卜 |    3 | 蔬菜 |
+----+------+------+------+

接着使用 CTE 的方式，完成上述功能：

mysql> WITH cte as (SELECT * FROM goods) SELECT g.*, (SELECT cte.name FROM cte WHERE cte.id = g.gid) AS gname FROM goods AS g;
+----+------+-----+------+
| id | name | gid | gname|
+----+------+-----+------+
|  1 | 商品 |   0 | NULL |
|  2 | 水果 |   1 | 商品 |
|  3 | 蔬菜 |   1 | 商品 |
|  4 | 苹果 |   2 | 水果 |
|  5 | 香蕉 |   2 | 水果 |
|  6 | 菠菜 |   3 | 蔬菜 |
|  7 | 萝卜 |   3 | 蔬菜 |
+----+------+-----+------+

从结果可以看出，CTE 是一个可以重复使用的结果集。相比于子查询，CTE 的效率会更高，因为非递归的 CTE 只会查询一次并可以重复使用。

CTE 可以引用其他 CTE 的结果。例如，下面的语句中，cte2 就引用了 cte1 中的结果。

mysql> with cte1 as (select * from goods), cte2 as (select g.*, cte1.name as gname from goods as g left join cte1 on g.gid = cte1.id) select * from cte2;
+----+------+-----+-------+
| id | name | gid | gname |
+----+------+-----+-------+
|  1 | 商品 |   0 | NULL  |
|  2 | 水果 |   1 | 商品  |
|  3 | 蔬菜 |   1 | 商品  |
|  4 | 苹果 |   2 | 水果  |
|  5 | 香蕉 |   2 | 水果  |
|  6 | 菠菜 |   3 | 蔬菜  |
|  7 | 萝卜 |   3 | 蔬菜  |
+----+------+-----+-------+

还有一种特殊的 CTE，就是递归 CTE，其子查询会引用自身。WITH 子句必须以 WITH RECURSIVE 开头。

CTE 递归子查询包括两部分：seed 查询和 recursive 查询，中间由 union [all] 或 union distinct 分隔。

seed 查询会被执行一次，以创建初始数据子集；
recursive 查询会被重复执行以返回数据子集，直到获得完整结果集。当迭代不会生成任何新行时，递归会停止。

可以参看下面的示例：

mysql> WITH RECURSIVE cte(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 8) SELECT * FROM cte;
+-------+
| n     |
+-------+
|    1  |
|    2  |
|    3  |
|    4  |
|    5  |
|    6  |
|    7  |
|    8  |
+-------+

上面的语句会递归显示 8 行，每行分别显示 1~8 数字。

递归的过程如下：

首先执行 SELECT 1 得到结果 1，即当前 n 的值为 1；
接着执行 SELECT N + 1 FROM cte WHERE n < 8，因为当前 n 为 1，所以WHERE条件成立，生成新行，SELECT n + 1得到结果 2，即当前 n 的值为 2；
继续执行 SELECT n + 1 FROM cte WHERE n < 8，因为当前 n 为 2，所以WHERE条件成立，生成新行，SELECT n + 1得到结果 3，即当前 n 的值为 3；
一直递归下去；
直到当 n 为 8 时，where 条件不成立，无法生成新行，递归停止。

下面使用递归 CTE 来查询每个商品到顶级商品的层次：

mysql>with recursive cte as (select id, name, cast('0' as char(255)) as path from goods where gid = 0 union all select goods.id,goods.name, concat(cte.path, ',', cte.id) as path from goods inner join cte on goods.gid = cte.id) select * from cte;
+------+-----+------+
| id   | name| path |
+------+-----+------+
|    1 | 商品| 0    |
|    2 | 水果| 0,1  |
|    3 | 蔬菜| 0,1  |
|    4 | 苹果| 0,1,2|
|    5 | 香蕉| 0,1,2|
|    6 | 菠菜| 0,1,3|
|    7 | 萝卜| 0,1,3|
+------+-----+------+

查询一个指定商品的所有父级商品：

mysql> with recursive cte as (select id, name, gid from goods where id = 7 union all select goods.id, goods.name, goods.gid from goods inner join cte on cte.gid = goods.id)select * from cte;
+-----+------+-----+
| id  | name | gid |
+-----+------+-----+
|   7 | 萝卜 |   3 |
|   3 | 蔬菜 |   1 |
|   1 | 商品 |   0 |
+-----+------+-----+

MySQL CTE通用表表达式的用法（非常详细）

相关文章