MySQL8功能详解——Common table expression (CTE)

转载

叶金荣 2021-07-14 15:24:20

文章标签 MySQL 文章分类 MySQL 数据库

Common table expression (CTE)通用表表达式是MySQL8推出的新功能。它是一种临时表，使用“WITH”命令，可以执行递归查询。

先看一下如何使用WITH语句：

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

看起来是不是像是将派生表放在了前面？是的，使用WITH语句，可以使你的查询看起来清晰明了，更加易读，但好处不止是这个，CTE可以多次参照。例如：

WITH d AS(SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b)SELECT ... FROM d AS d1 JOIN d AS d2 ON d1.b = d2.a;

也可以在其他CTE中引用CTE名称，从而使CTE能够基于其他CTE进行定义。例如：

WITH d1 AS(SELECT … FROM …),     d2 AS (SELECT … FROM d1 …)SELECTFROM d1, d2 …

此外，CTE可以引用自身来定义递归CTE。递归CTE常见于生成序列，层次或树状结构的遍历。例如：

打印1到10:

WITHRECURSIVE qn AS   ( SELECT 1 AS a     UNION ALL     SELECT 1+a FROM qn WHERE a<10   )SELECT *FROM qn;

插入1到10:

INSERT INTOnumbersWITHRECURSIVE qn AS   ( SELECT 1 AS a     UNION ALL     SELECT 1+a FROM qn WHERE a<10   )SELECT *FROM qn;

层次遍历：

CREATE TABLEemployees (      id INT PRIMARY KEY,      name VARCHAR(100),      manager_id INT,      FOREIGN KEY (manager_id)   REFERENCES employees(id) );

借用一下“蜀国”的人物充当一下员工

INSERT INTOemployees VALUES(333, "刘备", NULL), # (198, "关羽", 333), #(692, "张飞", 333),(29, "兵甲", 198),(4610,"兵乙", 29),(72, "兵丁", 29),(123, "兵己", 692);

执行一下查询：

WITHRECURSIVE emp_ext (id,name, path) AS (   SELECT id, name, CAST(id AS CHAR(200))   FROM employees   WHERE manager_id IS NULL UNION ALL   SELECT s.id, ,   CONCAT(m.path, ",", s.id)   FROM emp_ext m JOIN  employees s   ON =s.manager_id )   SELECT * FROM emp_ext ORDER BY path;

结果如下：

MySQL8功能详解——Common table expression (CTE)_MySQL