PostgreSQL Oracle兼容性之 - connect by-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

PostgreSQL Oracle兼容性之 - connect by

简介:

Oracle用户常用的功能之一connect by。
主要针对的是树形查询,例如上下级关系,家族图谱,分类,等。
1

用法举例
创建示例表:

CREATE TABLE TBL_TEST
(
ID    NUMBER,
NAME VARCHAR2(100 BYTE),
PID   NUMBER                                  DEFAULT 0
);

插入测试数据:

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

以pid为父ID,id为子ID,这几条记录的关系结构时这样的
4

从Root往树末梢递归

select * from TBL_TEST
start with id=1
connect by prior id = pid;

从1开始往下搜索,应该得到1,2,4,5

 id | name | pid 
----+------+-----
  1 | 10   |   0
  2 | 11   |   1
  5 | 121  |   2
  4 | 12   |   1
(4 rows)

从末梢往树ROOT递归

select * from TBL_TEST
start with id=5 
connect by prior pid = id;

从5开始往上搜索,应该得到5,2,1,0 (没有id=0的记录)

 id | name | pid 
----+------+-----
  5 | 121  |   2
  2 | 11   |   1
  1 | 10   |   0
(3 rows)

使用PostgreSQL with recursive可以达到同样的目的。
原理如下
2
3
有一个启动QUERY,得到的结果进入输出表
然后会进入第一次JOIN,用到输出表进行JOIN,输出的结果分别进入WORK TABLE和APPEND到输出表
然后进如第二次JOIN,用到WORK TABLE进行JOIN,清空WORK TABLE输出的结果分别进入WORK TABLE和APPEND到输出表
持续循环。

对应的SQL写法如下 :
创建示例表:

CREATE TABLE TBL_TEST
(
ID    numeric,
NAME text,
PID   numeric                                  DEFAULT 0
);

插入测试数据:

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归

with recursive t_result as (
  select * from tbl_test where id=1
    union all
  select t2.* from t_result t1 join tbl_test t2 on t1.id=t2.pid
)
select * from t_result;

 id | name | pid 
----+------+-----
  1 | 10   |   0
  2 | 11   |   1
  4 | 12   |   1
  5 | 121  |   2
(4 rows)

从末梢往树ROOT递归

with recursive t_result as (
  select * from tbl_test where id=5
    union all
  select t2.* from t_result t1 join tbl_test t2 on t1.pid=t2.id
)
select * from t_result;

 id | name | pid 
----+------+-----
  5 | 121  |   2
  2 | 11   |   1
  1 | 10   |   0
(3 rows)

树形结构的注意事项
.1. 一定要能跳出循环,即循环子句查不到结果为止。
.2. 树形结构如果有多个值,则会出现查到的结果比实际的多的情况,这个业务上是需要保证不出现重复的。
例子
再插几条记录

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

postgres=# select * from tbl_test order by pid;
 id | name | pid 
----+------+-----
  1 | 10   |   0
  3 | 20   |   0
  1 | 10   |   0
  4 | 12   |   1
  4 | 12   |   1
  2 | 11   |   1
  5 | 121  |   2
  5 | 121  |   2
(8 rows)

结构如下
5
因此在从ID=1向下搜索时,会得到这样的结果

 id | name | pid 
----+------+-----
  1 | 10   |   0
  1 | 10   |   0
  2 | 11   |   1
  2 | 11   |   1
  4 | 12   |   1
  4 | 12   |   1
  4 | 12   |   1
  4 | 12   |   1
  5 | 121  |   2
  5 | 121  |   2
  5 | 121  |   2
  5 | 121  |   2
(12 rows)

startup 得到2条记录

  1 | 10   |   0
  1 | 10   |   0

第一次JOIN得到

  2 | 11   |   1
  2 | 11   |   1
  4 | 12   |   1
  4 | 12   |   1
  4 | 12   |   1
  4 | 12   |   1

第二次JOIN得到

  5 | 121  |   2
  5 | 121  |   2
  5 | 121  |   2
  5 | 121  |   2

从ID=5向上搜索时,会得到这样的结果

 id | name | pid 
----+------+-----
  5 | 121  |   2
  5 | 121  |   2
  2 | 11   |   1
  2 | 11   |   1
  1 | 10   |   0
  1 | 10   |   0
  1 | 10   |   0
  1 | 10   |   0
(8 rows)

startup 得到2条记录

  5 | 121  |   2
  5 | 121  |   2

第一次JOIN得到

  2 | 11   |   1
  2 | 11   |   1

第二次JOIN得到

  1 | 10   |   0
  1 | 10   |   0
  1 | 10   |   0
  1 | 10   |   0

目前能做到这种查询的开源数据库不多,PostgreSQL作为学院派和工业界璀璨的明珠,从8.4开始就支持这种用法了。 赶紧试试你家的数据库能支持么?

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
最新文章
相关文章
链接