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

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

说明
  • CTE是一个命名的临时结果集,仅在单个SQL语句(例如SELECT、INSERT或DELETE)的执行范围内存在。
  • CTE仅在查询执行期间持续。

注意事项

  • CTE之后可以接SQL语句(例如SELECTINSERTUPDATE等)或者其他的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;