common table expression

Common table expression简称CTE，由SQL:1999标准引入，
目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), MariaDB (since 10.2), SQLite (since 3.8.3), HyperSQL and H2 (experimental), MySQL8.0.

CTE的语法如下：

以下图示来自MariaDB

Non-recursive CTEs

Recursive CTEs

CTE的使用

• CTE的可以使语句更加简洁
  

例如以下两个语句表达的是同一语义，使用CTE比未使用CTE的嵌套查询更简洁明了。

1) 使用嵌套子查询

2) 使用CTE

• CTE 可以进行树形查询
  

初始化这颗树

1) 层序遍历

2) 深度优先遍历

Oracle

Oracle从9.2才开始支持CTE, 但只支持non-recursive with, 直到Oracle 11.2才完全支持CTE。但oracle 之前就支持connect by 的树形查询，recursive with 语句可以与connect by语句相互转化。 一些相互转化案例可以参考这里.

Oracle recursive with 语句不需要指定recursive关键字，可以自动识别是否recursive.

Oracle 还支持CTE相关的hint,

“MATERIALIZE”告诉优化器生产一个全局的临时表保存结果，多次引用CTE时直接访问临时表即可。而"INLINE"则表示每次需要解析查询CTE。

PostgreSQL

PostgreSQL从8.4开始支持CTE，PostgreSQL还扩展了CTE的功能， CTE的query中支持DML语句，例如

MariaDB

MariaDB从10.2开始支持CTE。10.2.1 支持non-recursive CTE, 10.2.2开始支持recursive CTE。 目前的GA的版本是10.1.

MySQL

MySQL从8.0开始支持完整的CTE。MySQL8.0还在development
阶段，RC都没有，GA还需时日。

AliSQL

AliSQL port了mariadb10.2 no-recursive CTE的实现，此功能近期会上线。

以下从源码主要相关函数简要介绍其实现，

//解析识别with table引用
find_table_def_in_with_clauses

//检查依赖关系，比如不能重复定义with table名字
With_clause::check_dependencies

// 为每个引用clone一份定义
With_element::clone_parsed_spec

//替换with table指定的列名
With_element::rename_columns_of_derived_unit

此实现对于多次引用CTE，CTE会解析多次，因此此版本CTE有简化SQL的作用，但效率上没有效提高。

以下是MySQL8.0 只扫描一次的执行计划

以下是PostgreSQL9.4 只扫描一次的执行计划

AliSQL还在不断改进中，AliSQL的CTE即将推出，敬请期待！