• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • WITH(公用表表达式)

    公用表表达式(CTE)是一个命名的临时结果集,它存在于单个语句的范围内,以后可以在该语句中引用,可能多次。以下讨论描述了如何编写使用CTE的语句。

    • 常用表表达式
    • 递归公用表表达式
    • 限制公用表表达式递归
    • 递归公用表表达式示例
    • 公用表表达式与类似构造的比较

    有关CTE优化的信息,请参见“通过合并或实现来优化派生表,视图引用和公用表表达式”。

    其他资源

    这些文章包含有关在MySQL中使用CTE的其他信息,包括许多示例:

    • MySQL 8.0实验:[递归] MySQL(CTE)中的通用表表达式
    • MySQL 8.0实验:[递归] MySQL公用表表达式(CTE),第二部分-如何生成序列
    • MySQL 8.0实验:[递归] MySQL(CTE)中的通用表表达式,第三部分–层次结构
    • MySQL 8.0.1:MySQL(CTE)中的[递归]公用表表达式,第四部分–深度优先或广度优先遍历,传递闭包,循环避免

    常用表表达式

    要指定公用表表达式,请使用WITH具有一个或多个逗号分隔子句的子句。每个子节都提供一个子查询,该子查询产生一个结果集,并将一个名称与该子查询相关联。下面的示例定义名为的CTE cte1cte2WITH子句,并且是指在它们的顶层SELECT下面的WITH子句:

    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子句的语句中用作表引用。

    subquery部分称为“ CTE的子查询”,是产生CTE结果集的部分。需要以下括号。AS(subquery)AS

    如果公共表表达式的子查询引用了自己的名称,则该表表达式是递归的。该RECURSIVE关键字必须被包含,如果在任何CTE WITH条款是递归的。有关更多信息,请参见递归公用表表达式。

    确定给定CTE的列名的过程如下:

    • 如果带括号的名称列表位于CTE名称之后,则这些名称为列名称:

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

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

    • 否则,列名来自首的选择列表中SELECT的内部分:AS(subquery)

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

    WITH在以下情况下允许使用子句:

    • 在开始时SELECTUPDATEDELETE语句。

      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引用cte2cte2引用cte1。这些引用之一必须是稍后定义的CTE,这是不允许的。

    • 给定查询块中的CTE可以引用在更外部级别的查询块中定义的CTE,但不能引用在更内部级别的查询块中定义的CTE。

    为了解析对具有相同名称的对象的引用,派生表会隐藏CTE。CTE隐藏基本表,TEMPORARY表和视图。通过在同一查询块中搜索对象来进行名称解析,然后依次搜索外部块,而找不到具有该名称的对象。

    像派生表一样,CTE不能包含MySQL 8.0.14之前的外部引用。这是MySQL 8.0.14中取消的MySQL限制,而不是SQL标准的限制。有关特定于递归CTE的其他语法注意事项,请参阅递归公用表表达式。

    递归公用表表达式

    递归公用表表达式是具有引用其自身名称的子查询的表达式。例如:

    WITH RECURSIVE cte (n) AS
    (
      SELECT 1
      UNION ALL
      SELECT n + 1 FROM cte WHERE n < 5
    )
    SELECT * FROM cte;
    

    执行后,该语句将产生以下结果,即包含简单线性序列的单列:

    +------+
    | n    |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    |    5 |
    +------+
    

    递归CTE具有以下结构:

    • 如果该WITH子句中的WITH RECURSIVE任何CTE WITH指向其自身,则该子句必须以其开头。(如果没有CTE引用自己,RECURSIVE则允许,但不是必需的。)

      如果您忘记RECURSIVE了递归CTE,则可能会出现以下错误:

      ERROR 1146 (42S02): Table 'cte_name' doesn't exist
      
    • 递归CTE子查询分为两部分,用UNION[ALL]或分隔UNION DISTINCT

      SELECT ...      -- return initial row set
      UNION ALL
      SELECT ...      -- return additional row sets
      

      第一个SELECT生成CTE的初始行或多个行,并且不引用CTE名称。第二个SELECT通过引用其FROM子句中的CTE名称产生其他行并递归。当此部分不产生新行时,递归结束。因此,递归CTE由一个非递归SELECT部分和一个递归SELECT部分组成。

      每个SELECT部分本身可以是多个SELECT语句的并集。

    • CTE结果列的类型SELECT只能从非递归部分的列类型中推断出来,并且这些列都是可空的。对于类型确定,将SELECT忽略递归部分。
    • 如果非递归和递归部分之间用分隔UNION DISTINCT,则将消除重复的行。这对于执行传递闭包的查询很有用,以避免无限循环。
    • 递归部分的每次迭代仅对先前迭代产生的行进行操作。如果递归部分具有多个查询块,则每个查询块的迭代将以未指定的顺序进行调度,并且每个查询块将对从上一次迭代结束后由其上一次迭代或其他查询块生成的行进行操作。

    前面显示的递归CTE子查询具有以下非递归部分,该部分检索单个行以产生初始行集:

    SELECT 1
    

    CTE子查询还具有以下递归部分:

    SELECT n + 1 FROM cte WHERE n < 5
    

    在每次迭代中,这将SELECT产生一行新值,该行的新值比n上一行中的值大一个。第一次迭代在初始行集(1)上操作并产生1+1=2;第二次迭代在第一个迭代的行集(2)上操作并产生2+1=3;等等。这一直持续到递归结束为止,递归n不再小于5。

    如果CTE的递归部分为列生成的值比非递归部分宽,则可能有必要加宽非递归部分中的列以避免数据被截断。考虑以下语句:

    WITH RECURSIVE cte AS
    (
      SELECT 1 AS n, 'abc' AS str
      UNION ALL
      SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
    )
    SELECT * FROM cte;
    

    在非严格SQL模式下,该语句产生以下输出:

    +------+------+
    | n    | str  |
    +------+------+
    |    1 | abc  |
    |    2 | abc  |
    |    3 | abc  |
    +------+------+
    

    str列值都是'abc'因为非递归SELECT确定列宽。因此,str递归产生的较宽的值将SELECT被截断。

    在严格的SQL模式下,该语句产生错误:

    ERROR 1406 (22001): Data too long for column 'str' at row 1
    

    要解决此问题,以使语句不会产生截断或错误,请CAST()在非递归中使用SELECT以使str列变宽:

    WITH RECURSIVE cte AS
    (
      SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
      UNION ALL
      SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
    )
    SELECT * FROM cte;
    

    现在,该语句产生以下结果,而不会截断:

    +------+--------------+
    | n    | str          |
    +------+--------------+
    |    1 | abc          |
    |    2 | abcabc       |
    |    3 | abcabcabcabc |
    +------+--------------+
    

    列是通过名称而不是位置来访问的,这意味着递归部分中的列可以访问非递归部分中具有不同位置的列,如本CTE所示:

    WITH RECURSIVE cte AS
    (
      SELECT 1 AS n, 1 AS p, -1 AS q
      UNION ALL
      SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
    )
    SELECT * FROM cte;
    

    因为p一行是从q上一行派生的,反之亦然,所以正值和负值会交换输出的每个连续行中的位置:

    +------+------+------+
    | n    | p    | q    |
    +------+------+------+
    |    1 |    1 |   -1 |
    |    2 |   -2 |    2 |
    |    3 |    4 |   -4 |
    |    4 |   -8 |    8 |
    |    5 |   16 |  -16 |
    +------+------+------+
    

    一些语法约束适用于递归CTE子查询:

    • 递归SELECT部分不得包含以下构造:

      • 汇总功能,例如SUM()
      • 视窗功能
      • GROUP BY
      • ORDER BY
      • DISTINCT

      在MySQL 8.0.19之前,SELECT递归CTE 的递归部分也不能使用LIMIT子句。MySQL 8.0.19中取消了此限制,并且LIMIT在这种情况下,现在还支持此限制以及可选OFFSET子句。对结果集的影响与LIMIT在最外层使用时相同SELECT,但效率更高,因为与递归结合使用时,SELECT一旦产生了所需数量的行,行就会停止生成。

      这些约束不适用于SELECT递归CTE 的非递归部分。禁止DISTINCT仅适用于UNION会员;UNION DISTINCT被允许。

    • 递归SELECT部分必须仅在其FROM子句中引用一次CTE ,而不能在任何子查询中引用。它可以引用CTE以外的表,并将它们与CTE联接在一起。如果在这样的联接中使用,则CTE不得位于的右侧LEFT JOIN

    这些约束来自于SQL标准,比的MySQL特定排除其他ORDER BYLIMIT(MySQL的8.0.18和更早的版本),和DISTINCT

    对于递归CTE,在列中显示EXPLAIN递归SELECT零件的输出行。RecursiveExtra

    显示的成本估算值EXPLAIN代表每次迭代的成本,可能与总成本有很大不同。优化器无法预测迭代次数,因为它无法预测该WHERE子句何时变为假。

    CTE实际成本也可能会受到结果集大小的影响。产生许多行的CTE可能需要一个足够大的内部临时表,以将其从内存中转换为磁盘上的格式,并且可能会降低性能。如果是这样,则增加允许的内存中临时表大小可能会提高性能;请参见“ MySQL中的内部临时表使用”。

    限制公用表表达式递归

    对于递归CTE,重要的是递归SELECT部分包括终止递归的条件。作为一种防止递归CTE失控的开发技术,您可以通过限制执行时间来强制终止:

    • cte_max_recursion_depth系统变量强制执行递归级别的热膨胀系数为数量限制。服务器终止任何递归级别高于此变量值的CTE。
    • 所述max_execution_time系统变量强制用于执行超时SELECT在当前会话中执行的语句。
    • MAX_EXECUTION_TIME优化器提示强制为每个查询执行超时SELECT在它出现的语句。

    假设在没有递归执行终止条件的情况下错误地编写了递归CTE:

    WITH RECURSIVE cte (n) AS
    (
      SELECT 1
      UNION ALL
      SELECT n + 1 FROM cte
    )
    SELECT * FROM cte;
    

    默认情况下,cte_max_recursion_depth值为1000,导致CTE递归超过1000级时终止。应用程序可以更改会话值以适应其要求:

    SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
    SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion
    

    您还可以设置全局cte_max_recursion_depth值以影响随后开始的所有会话。

    对于执行缓慢并因此递归的查询,或者在有理由将其cte_max_recursion_depth值设置得很高的上下文中,防止深度递归的另一种方法是设置每个会话超时。为此,请在执行CTE语句之前执行如下语句:

    SET max_execution_time = 1000; -- impose one second timeout
    

    或者,在CTE语句本身中包含优化程序提示:

    WITH RECURSIVE cte (n) AS
    (
      SELECT 1
      UNION ALL
      SELECT n + 1 FROM cte
    )
    SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;
    
    WITH RECURSIVE cte (n) AS
    (
      SELECT 1
      UNION ALL
      SELECT n + 1 FROM cte
    )
    SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
    

    从MySQL 8.0.19开始,您还可以LIMIT在递归查询中使用,以将最大数目的行强加给最外层SELECT,例如:

    WITH RECURSIVE cte (n) AS
    (
      SELECT 1
      UNION ALL
      SELECT n + 1 FROM cte LIMIT 10000
    )
    SELECT * FROM cte;
    

    除了设置时间限制以外,还可以设置时间限制。因此,以下CTE在返回一万行或运行一千秒后(以先到者为准)终止:

    WITH RECURSIVE cte (n) AS
    (
      SELECT 1
      UNION ALL
      SELECT n + 1 FROM cte LIMIT 10000
    )
    SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
    

    如果没有执行时间限制的递归查询进入无限循环,则可以使用终止另一个会话的查询KILL QUERY。在会话本身内,用于运行查询的客户端程序可能提供一种杀死查询的方法。例如,在mysql中,键入Control + C会中断当前语句。

    递归公用表表达式示例

    如前所述,递归公用表表达式(CTE)通常用于序列生成和遍历分层或树结构数据。本节显示了这些技术的一些简单示例。

    • 斐波那契数列生成
    • 日期系列生成
    • 分层数据遍历

    斐波那契数列生成

    斐波那契数列以两个数字0和1(或1和1)开始,其后的每个数字是前两个数字的和。如果递归产生的每一行都SELECT可以访问该序列中的前两个数字,则递归公用表表达式可以生成斐波那契数列。以下CTE使用0和1作为前两个数字来生成10数系列:

    WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
    (
      SELECT 1, 0, 1
      UNION ALL
      SELECT n + 1, next_fib_n, fib_n + next_fib_n
        FROM fibonacci WHERE n < 10
    )
    SELECT * FROM fibonacci;
    

    CTE产生以下结果:

    +------+-------+------------+
    | n    | fib_n | next_fib_n |
    +------+-------+------------+
    |    1 |     0 |          1 |
    |    2 |     1 |          1 |
    |    3 |     1 |          2 |
    |    4 |     2 |          3 |
    |    5 |     3 |          5 |
    |    6 |     5 |          8 |
    |    7 |     8 |         13 |
    |    8 |    13 |         21 |
    |    9 |    21 |         34 |
    |   10 |    34 |         55 |
    +------+-------+------------+
    

    CTE的工作方式:

    • n是显示列,指示该行包含n-th斐波那契数。例如,第8个斐波那契数是13。
    • fib_n列显示斐波纳契数n
    • next_fib_n列显示number之后的下一个斐波那契数n。该列为下一行提供了下一个序列值,因此该行可以在其fib_n列中产生前两个序列值的总和。
    • 递归在n到达10 时结束。这是一个任意选择,可以将输出限制为一小组行。

    前面的输出显示了整个CTE结果。要仅选择其中一部分,请WHERE在顶层添加一个适当的子句SELECT。例如,要选择第8个斐波那契数,请执行以下操作:

    mysql> WITH RECURSIVE fibonacci ...
           ...
           SELECT fib_n FROM fibonacci WHERE n = 8;
    +-------	+
    | fib_n 	|
    +-------	+
    |    13 	|
    +-------	+
    

    日期系列生成

    公用表表达式可以生成一系列连续的日期,这对于生成摘要(包括该系列中所有日期的一行,包括未在汇总数据中表示的日期)非常有用。

    假设销售数字表包含以下行:

    mysql> SELECT * FROM sales ORDER BY date, price;
    +------------	+--------	+
    | date       	| price  	|
    +------------	+--------	+
    | 2017	-01	-03 	| 100.00 	|
    | 2017	-01	-03 	| 200.00 	|
    | 2017	-01	-06 	|  50.00 	|
    | 2017	-01	-08 	|  10.00 	|
    | 2017	-01	-08 	|  20.00 	|
    | 2017	-01	-08 	| 150.00 	|
    | 2017	-01	-10 	|   5.00 	|
    +------------	+--------	+
    

    该查询总结了每天的销售额:

    mysql> SELECT date, SUM(price) AS sum_price
           FROM sales
           GROUP BY date
           ORDER BY date;
    +------------	+-----------	+
    | date       	| sum_price 	|
    +------------	+-----------	+
    | 2017	-01	-03 	|    300.00 	|
    | 2017	-01	-06 	|     50.00 	|
    | 2017	-01	-08 	|    180.00 	|
    | 2017	-01	-10 	|      5.00 	|
    +------------	+-----------	+
    

    但是,该结果在表所跨越的日期范围内未包含的日期中包含“空洞”。可以使用递归CTE生成代表日期范围的所有日期的结果,以生成日期集,并与LEFT JOIN销售数据结合使用。

    这是生成日期范围系列的CTE:

    WITH RECURSIVE dates (date) AS
    (
      SELECT MIN(date) FROM sales
      UNION ALL
      SELECT date + INTERVAL 1 DAY FROM dates
      WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
    )
    SELECT * FROM dates;
    

    CTE产生以下结果:

    +------------+
    | date       |
    +------------+
    | 2017-01-03 |
    | 2017-01-04 |
    | 2017-01-05 |
    | 2017-01-06 |
    | 2017-01-07 |
    | 2017-01-08 |
    | 2017-01-09 |
    | 2017-01-10 |
    +------------+
    

    CTE的工作方式:

    • 非递归SELECT在该sales表跨越的日期范围内产生最低日期。
    • 递归产生的每一行都SELECT将前一天产生的日期增加一天。
    • 当日期达到该sales表跨越的日期范围内的最高日期后,递归结束。

    用加入CTE LEFT JOINsales表产生与一排范围内的每个日期的销售汇总:

    WITH RECURSIVE dates (date) AS
    (
      SELECT MIN(date) FROM sales
      UNION ALL
      SELECT date + INTERVAL 1 DAY FROM dates
      WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
    )
    SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
    FROM dates LEFT JOIN sales ON dates.date = sales.date
    GROUP BY dates.date
    ORDER BY dates.date;
    

    输出看起来像这样:

    +------------+-----------+
    | date       | sum_price |
    +------------+-----------+
    | 2017-01-03 |    300.00 |
    | 2017-01-04 |      0.00 |
    | 2017-01-05 |      0.00 |
    | 2017-01-06 |     50.00 |
    | 2017-01-07 |      0.00 |
    | 2017-01-08 |    180.00 |
    | 2017-01-09 |      0.00 |
    | 2017-01-10 |      5.00 |
    +------------+-----------+
    

    需要注意的几点:

    • 查询是否效率低下,尤其是MAX()对于递归的每一行执行子查询的查询SELECT?进行检查,EXPLAIN表明子查询已针对效率进行了优化。
    • 使用的COALESCE()显示避免NULLsum_price天列发生在没有销售数据sales表。

    分层数据遍历

    递归公用表表达式对于遍历形成层次结构的数据很有用。考虑以下这些语句,这些语句创建了一个小的数据集,该数据集为公司中的每个员工显示了员工姓名和ID号以及员工经理的ID。顶层员工(CEO)的经理ID为NULL(无经理)。

    CREATE TABLE employees (
      id         INT PRIMARY KEY NOT NULL,
      name       VARCHAR(100) NOT NULL,
      manager_id INT NULL,
      INDEX (manager_id),
    FOREIGN KEY (manager_id) REFERENCES employees (id)
    );
    INSERT INTO employees VALUES
    (333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
    (198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
    (692, "Tarek", 333),
    (29, "Pedro", 198),
    (4610, "Sarah", 29),
    (72, "Pierre", 29),
    (123, "Adil", 692);
    

    结果数据集如下所示:

    mysql> SELECT * FROM employees ORDER BY id;
    +------	+---------	+------------	+
    | id   	| name    	| manager_id 	|
    +------	+---------	+------------	+
    |   29 	| Pedro   	|        198 	|
    |   72 	| Pierre  	|         29 	|
    |  123 	| Adil    	|        692 	|
    |  198 	| John    	|        333 	|
    |  333 	| Yasmina 	|       NULL 	|
    |  692 	| Tarek   	|        333 	|
    | 4610 	| Sarah   	|         29 	|
    +------	+---------	+------------	+
    

    要生成具有每个员工管理链的组织结构图(即从CEO到员工的路径),请使用递归CTE:

    WITH RECURSIVE employee_paths (id, name, path) AS
    (
      SELECT id, name, CAST(id AS CHAR(200))
        FROM employees
        WHERE manager_id IS NULL
      UNION ALL
      SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
        FROM employee_paths AS ep JOIN employees AS e
          ON ep.id = e.manager_id
    )
    SELECT * FROM employee_paths ORDER BY path;
    

    CTE产生以下输出:

    +------+---------+-----------------+
    | id   | name    | path            |
    +------+---------+-----------------+
    |  333 | Yasmina | 333             |
    |  198 | John    | 333,198         |
    |   29 | Pedro   | 333,198,29      |
    | 4610 | Sarah   | 333,198,29,4610 |
    |   72 | Pierre  | 333,198,29,72   |
    |  692 | Tarek   | 333,692         |
    |  123 | Adil    | 333,692,123     |
    +------+---------+-----------------+
    

    CTE的工作方式:

    • 非递归SELECT产生CEO行(具有NULL经理ID 的行)。

      所述path柱被加宽,以CHAR(200)确保有足够的空间对于较长path通过递归产生的值SELECT

    • 递归产生的每一行都会SELECT找到直接向上一行产生的雇员报告的所有雇员。对于每个这样的员工,该行包括员工ID和姓名以及员工管理链。该链是经理的链,最后添加了员工ID。
    • 当员工没有其他人向其报告时,递归结束。

    要查找特定雇员或多名雇员的路径,请WHERE在顶层添加一个子句SELECT。例如,要显示Tarek和Sarah的结果,请进行如下修改SELECT

    mysql> WITH RECURSIVE ...
           ...
           SELECT * FROM employees_extended
           WHERE id IN (692, 4610)
           ORDER BY path;
    +------	+-------	+-----------------	+
    | id   	| name  	| path            	|
    +------	+-------	+-----------------	+
    | 4610 	| Sarah 	| 333,198,29,4610 	|
    |  692 	| Tarek 	| 333,692         	|
    +------	+-------	+-----------------	+
    

    公用表表达式与类似构造的比较

    公用表表达式(CTE)在某些方面类似于派生表:

    • 两种结构都被命名。
    • 两种构造都存在于单个语句的范围内。

    由于这些相似之处,CTE和派生表通常可以互换使用。作为一个简单的例子,这些语句是等效的:

    WITH cte AS (SELECT 1) SELECT * FROM cte;
    SELECT * FROM (SELECT 1) AS dt;
    

    但是,CTE与派生表相比具有一些优势:

    • 派生表只能在查询中一次引用。可以多次引用CTE。要使用派生表结果的多个实例,您必须多次派生结果。
    • CTE可以是自引用的(递归的)。
    • 一个CTE可以引用另一个。
    • 当CTE的定义出现在语句的开始而不是嵌入在语句的开头时,可能更易于阅读。

    CTE与使用创建的表相似,CREATE[TEMPORARY] TABLE但无需明确定义或删除。对于CTE,您不需要创建表的特权。

    上篇:VALUES语句