CTE（Common Table Expressions）是指使用WITH语句定义的通用表表达式。 如: ``` testdb=# explain verbose WITH t1 AS ( SELECT * FROM t_w1 WHERE t_w1.id % 4 = 0 ) SELECT * FROM t1 JOIN t_w2 as t2 ON t2.id = t1.id; QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=167.74..359.00 rows=76 width=70) Output: t1.id, t1.c1, t2.id, t2.c1 Hash Cond: (t2.id = t1.id) CTE t1 -> Seq Scan on public.t_w1 (cost=0.00..166.50 rows=38 width=8) Output: t_w1.id, t_w1.c1 Filter: ((t_w1.id % 4) = 0) -> Seq Scan on public.t_w2 t2 (cost=0.00..153.00 rows=10000 width=8) Output: t2.id, t2.c1 -> Hash (cost=0.76..0.76 rows=38 width=62) Output: t1.id, t1.c1 -> CTE Scan on t1 (cost=0.00..0.76 rows=38 width=62) Output: t1.id, t1.c1 (13 rows) ``` 使用CTE可以: 1.增强SQL的可读性:如上例所示,通过CTE,可以"模块化"SQL语句,增强脚本可读性 2.实现递归:通过增加RECURSIVE修饰符来引入它自己，从而实现递归 **递归** 递归通常用于处理逻辑上存在层次或树状结构的数据. 如: ``` drop table if exists t_cte; create table t_cte(id varchar(10),parent_id varchar(10)); insert into t_cte values('1',NULL); insert into t_cte values('11','1'); insert into t_cte values('12','1'); insert into t_cte values('111','11'); insert into t_cte values('112','11'); insert into t_cte values('121','12'); ``` id为数据表的id,parent_id是该id的父id,通过该字段可找到对应的父记录,先要求打印这些数据的树状结构,相应的SQL语句如下: ``` WITH RECURSIVE ret AS ( SELECT parent_id, id::text as name, id::text FROM t_cte WHERE id = '1' UNION ALL SELECT t.parent_id, t.parent_id || ' > ' || t.id as name, t.id FROM ret JOIN t_cte t ON t.parent_id = ret.id ) SELECT parent_id, name FROM ret; ``` WITH RECURSIVE语句包含两个部分 1.non-recursive term（非递归部分） 即上例中的: ``` SELECT parent_id, id::text as name, id::text FROM t_cte WHERE id = '1' ``` 2.recursive term（递归部分） 即上例中的: ``` SELECT t.parent_id, t.parent_id || ' > ' || t.id as name, t.id FROM ret JOIN t_cte t ON t.parent_id = ret.id ``` 执行步骤如下 1.执行non-recursive term。其结果作为recursive term中对ret的引用，同时将这部分结果放入工作表中 2.重复执行如下步骤，直到工作表为空：用工作表的内容替换递归的自引用(上例中的ret)，执行recursive term，并用该结果替换工作表 大凡递归, 必须有结束条件,工作表为空则是CTE Recursive的结束条件.就上例来说, ``` SELECT t.parent_id, t.parent_id || ' > ' || t.id as name, t.id FROM ret JOIN t_cte t ON t.parent_id = ret.id ``` 返回为空时,递归结束.