CTE

CTE（Common Table Expressions，即公共表表达式）是一个可以命名的临时结果集，不实际作为对象存储；其作用范围是当前语句，仅可在查询执行期间使用。
CTE一般用于相同子查询的复用、简化递归查询或者可以将复杂的嵌套查询拆分为多个逻辑更清晰的子句。

语法

使用 WITH 语句创建公共表表达式，语法为

WITH [RECURSIVE] 
  with_query_name [(col_name [, ...] )] AS ( select | values | insert | update | delete )
  [, ...] 
  ...

参数说明

• RECURSIVE, 递归，如果指定了RECURSIVE，则允许一个 SELECT 子查询使用名称引用自身。
• with_query_name, 公共表表达式的名称，可被包含 WITH 子句的的表引用。
• column_name，公共表表达式的列名， 可在之后的语句中直接引用。

递归的CTE

递归CTE用于处理具有递归结构的数据，例如树形结构、图形等。其WITH子句必须以RECURSIVE 开头，同时AS之后的语句需要使用UNION或UNION ALL将未引用CTE的SELECT和引用了CTE的SELECT 联合起来, 且未引用CTE的SELECT要放在前面。

语法

WITH RECURSIVE cte(col) AS (
subquery1
UNION [ALL]
subquery2
)

这里，subquery1 用来产生初始结果集， 然后用该结果作为条件，递归执行 subquery2，当 subquery2 不再产生新的结果集时，执行结束。

示例1

WITH RECURSIVE cte (col) AS
(
  SELECT 1               -- 非递归部分，生成初始行集
  UNION ALL
  SELECT col + 3 FROM cte WHERE col < 20     -- 递归部分，col 初始为 1，然后 +3 递归执行，直到小于20，结束
)
SELECT * FROM cte;

--执行结果：
 COL 
-----
   1
   4
   7
  10
  13
  16
  19
  22
(8 rows)

非递归的CTE

与递归CTE相对应的，如果一个CTE的AS子句中，没有引用自身，那么该CTE被称为非递归 CTE。

示例2

WITH cte AS (
SELECT gender, count(*) FROM employees GROUP BY gender 
) SELECT * FROM cte;

--查询结果
 GENDER | COUNT 
--------+-------
 female |     3
 male   |     8
(2 rows)