Oracle转PostgreSQL之start with / connect by

Oracle分层查询

Oracle中start with / connect by提供分层查询的能力,从START WITH开始遍历记录,递归查询结果集直到拿到所有满足条件的结果。

例如下面测试数据:

drop table sr_menu;
create table sr_menu(  
  id number(10) not null, 
  parent number(10),
  title varchar2(50)
);
insert into sr_menu values (1, null, 'level 0');
insert into sr_menu values (2, 1, 'level 1');
insert into sr_menu values (3, 1, 'level 1');
insert into sr_menu values (4, 1, 'level 1');
insert into sr_menu values (5, 3, 'level 2');
commit;

select * from sr_menu;

        ID     PARENT TITLE                                             
---------- ---------- --------------------------------------------------
         1            level 0                                           
         2          1 level 1                                           
         3          1 level 1                                           
         5          3 level 2                                           
         4          1 level 1 

有这样的逻辑

level 0:          1
            /   /   \   \
level 1:   2   3     4   5
              /
level 2:     5

业务上如果需要查询跟节点1所以的子节点,在Oracle中可以使用以下语法:

select * from sr_menu 
start with id = 1 
connect by prior id = parent;
        ID     PARENT TITLE                                             
---------- ---------- --------------------------------------------------
         1            level 0                                           
         2          1 level 1                                           
         3          1 level 1                                           
         5          3 level 2                                           
         4          1 level 1  

查询时会用上一层的id=1(prior修饰)和当前的parent比较,查询出第二层符合条件的数据:

         2          1 level 1                                           
         3          1 level 1 
         4          1 level 1  

后面继续递归,使用上一层的id=2/3/4去匹配下面的数据,从id=3中得到:

         5          3 level 2                                           

PostgreSQL分层查询改造

Oracle 分层查询其实是一种递归查询的方式,用第一层查询的结果递归出后一层。在 Postgresql 中可以使用 WITH RECURSIVE 语法实现相同的功能。

普通的 WITH 子句可以实现 CTE 的功能,加上 RECURSIVE 关键字可以进一步在 WITH 内引用自己的输出实现递归,例如对于上面 SQL 的改写,可以实现完全相同的业务逻辑:

WITH RECURSIVE a AS (
SELECT id, parent, title
  FROM sr_menu
  WHERE id = 1
UNION ALL
  SELECT d.id, d.parent, d.title
  FROM sr_menu d
  JOIN a ON a.id = d.parent )
SELECT id, parent, title FROM a;

 id | parent |  title
----+--------+---------
  1 |        | level 0
  2 |      1 | level 1
  3 |      1 | level 1
  4 |      1 | level 1
  5 |      3 | level 2

WITH 内使用 UNION ALL 的第一张对应 START WITH语句,一般是一个固定结果集的查询条件。

UNION ALL的第二张表join ... a.id,表示连接当前 with 子句的查询结果,这样反复递归直到所有数据查询完毕。

从递归深度也可以看出执行过程:

WITH RECURSIVE a AS (
SELECT id, parent, title, 1::integer recursion_level
  FROM sr_menu
  WHERE id = 1
UNION ALL
  SELECT d.id, d.parent, d.title, a.recursion_level +1
  FROM sr_menu d
  JOIN a ON a.id = d.parent )
SELECT * FROM a;

 id | parent |  title  | recursion_level
----+--------+---------+-----------------
  1 |        | level 0 |               1
  2 |      1 | level 1 |               2
  3 |      1 | level 1 |               2
  4 |      1 | level 1 |               2
  5 |      3 | level 2 |               3

有关WITH RECURSIVE

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

递归WITH的执行流程:

  1. 计算非递归项(UNION ALL内的固定查询部分,例如上面的VALUES(1))把结果放在临时表A中
  2. 临时表不为空,重复下列步骤:
    1. 计算递归项(UNION ALL 内的递归部分),用临时表A当作递归自引用表。查询结果记录到临时表B
    2. 用B的数据库覆盖A,清空B
上一篇:vue权限控制——动态路由


下一篇:ant design vue pro中,人为控制菜单选中状态(包括操作子路由时让父级菜单选中状态)