ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(35) - CTE

PostgreSQL DBA(35) - CTE

原创 PostgreSQL 作者:husthxd 时间:2019-06-03 18:40:36 0 删除 编辑

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

返回为空时,递归结束.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2646635/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1267
  • 访问量
    3745268