全部产品
云市场

WITH

更新时间:2019-07-02 20:07:46

本文介绍如何在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使用方法

  • 以下两个查询等价

    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;