公共表表达式 (CTE)

公用表表达式(CTE,Common table expression) 是一个命名的临时结果集,仅在单个 SQL 语句 (例如 SELECTINSERTUPDATEDELETE) 的执行范围内存在。

与派生表类似,CTE 不作为对象存储,仅在查询执行期间持续;与派生表不同,CTE 可以是自引用 (递归 CTE,暂时不支持),也可以在同一查询中多次引用。此外,与派生表相比,CTE 提供了更好的可读性和性能。

CTE 的结构包括名称,可选列列表和定义 CTE 的查询。

CTE 语法如下:

  1. WITH <query_name> AS (
  2. <query_definition>
  3. )
  4. SELECT ... FROM <query_name>;

开始前准备

你需要确认在开始之前,已经完成了以下任务:

已完成单机部署 MatrixOne

数据准备

你可以新建一个简单的表,插入一些数据,帮助你理解后续所展示的 CTE 语句:

  1. drop table if exists t1;
  2. create table t1(a int, b int, c int);
  3. insert into t1 values(null,null,null),(2,3,4);

CTE 语句使用实例

在下面的示例中,qn 作为一个临时的结果集被创建,此时相应的查询结果会被缓存在 MatrixOne 中,你在执行正式的 qn 查询时,比非 CTE 场景的性能有所提升。

  1. mysql> WITH qn AS (SELECT a FROM t1), qn2 as (select b from t1)
  2. SELECT * FROM qn;

查询结果如下:

  1. +------+
  2. | a |
  3. +------+
  4. | NULL |
  5. | 2 |
  6. +------+
  7. 2 rows in set (0.00 sec)