PostgreSQL Oracle兼容性之 - connect by

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

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开始就支持这种用法了。 赶紧试试你家的数据库能支持么?

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
6天前
|
SQL 监控 Oracle
关系型数据库Oracle并行执行
【7月更文挑战第12天】
34 14
|
6天前
|
SQL Oracle 关系型数据库
|
5天前
|
Oracle 关系型数据库 数据处理
|
5天前
|
SQL 监控 Oracle
|
5天前
|
SQL Oracle 关系型数据库
|
2天前
|
安全 Oracle 关系型数据库
关系型数据库Oracle安全性
【7月更文挑战第17天】
11 5
|
2天前
|
存储 Oracle 关系型数据库
关系型数据库Oracle备份与恢复
【7月更文挑战第17天】
19 4
|
4天前
|
存储 监控 Oracle
关系型数据库Oracle空间不足
【7月更文挑战第15天】
23 6
|
4天前
|
监控 Oracle 关系型数据库
关系型数据库Oracle常见问题
【7月更文挑战第15天】
14 6
|
1天前
|
Oracle 关系型数据库 数据库
关系型数据库Oracle增量备份
【7月更文挑战第18天】
7 2

相关产品

  • 云原生数据库 PolarDB
  • 推荐镜像

    更多