全部产品
云市场

CTE

更新时间:2018-12-18 11:35:22

CTE是一个命名的临时结果集,仅在单个SQL语句(例如SELECT,INSERT或DELETE)的执行范围内存在。CTE不作为对象存储,仅在查询执行期间持续,提供了更好的可读性和性能。

可以使用WITH子句来创建CTE,WITH子句用于定义一个子查询关系,以供SELECT查询引用,它可以扁平化嵌套查询或者简化子查询。使用WITH子句定义一个子查询后,SELECT只需要执行一遍这个子查询即可,提高查询性能。

以下两个查询是等价的:

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

WITH子句同样适用于多子查询:

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

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

  1. WITH
  2. x AS (SELECT a FROM t),
  3. y AS (SELECT a AS b FROM x),
  4. z AS (SELECT b AS c FROM y)
  5. SELECT c FROM z;

注意事项

  • CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则CTE将失效。
  • CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔
  • CTE语句里暂时不支持分页