本文介绍如何在SELECT语句中使用WITH子句。

查询中可以使用WITH子句来创建通用表达式（Common Table Express， 简称CTE），WITH子句定义的子查询，供SELECT查询引用。WITH子句可以扁平化嵌套查询或者简化子查询，SELECT只需执行一遍子查询，提高查询性能。

• CTE是一个命名的临时结果集，仅在单个SQL语句（例如SELECT、INSERT或DELETE）的执行范围内存在。

• CTE仅在查询执行期间持续。

注意事项

• CTE之后可以接SQL语句（例如SELECT、INSERT或UPDATE等）或者其他的CTE（只能使用一个WITH），多个CTE中间用逗号（,），否则CTE将失效。

• CTE语句中暂不支持分页功能。

WITH使用方法

• 以下两个查询等价

    SELECT a, b
    FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;

    WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
    SELECT a, b FROM x;

• WITH子句可用于多子查询：

    WITH
    t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
    t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
    SELECT t1.*, t2.*
    FROM t1 JOIN t2 ON t1.a = t2.a;

• WITH子句中定义的关系可以互相连接

    WITH
    x AS (SELECT a FROM t),
    y AS (SELECT a AS b FROM x),
    z AS (SELECT b AS c FROM y)
    SELECT c FROM z;