问题现象
with 语句包含递归sql 改写。
处理方法
示例1
create table A_ORGAN(id int,name varchar(16),parent int);
insert into A_ORGAN values(1,'南大通用',0);
insert into A_ORGAN values(2,'行销部',1);
insert into A_ORGAN values(3,'专业服务组',2);
insert into A_ORGAN values(4,'售后组',2);
with rpl(id,name,parent) as
(select id, name, parent
from A_ORGAN
where parent =1
union all
select t1.id, t1.name, t1.parent
from rpl t2, A_ORGAN t1
where t2.id = t1.parent)
select * from rpl
GBase 8a MPP Cluster 最佳实践
5 FAQ
文档版本（2022-02-11）
南大通用数据技术股份有限公司
116
oracle 中执行结果
SQL> with rpl(id,name,parent) as
2
(select id, name, parent
3
from A_ORGAN
4
where parent =1
5
union all
6
select t1.id, t1.name, t1.parent
7
from rpl t2, A_ORGAN t1
8
where t2.id = t1.parent)
9
select * from rpl ;
ID NAME
PARENT
---------- ---------------- ----------
2 行销部
1
3 专业服务组
2
4 售后组
2
SQL>
示例2
在GBase 8a 中目前不支持这种复杂的with 语句，使用START WITH CONNECT
BY 改写，由于当前限制只支持复制表，改写如下：
create table A_ORGAN1(id int,name varchar(16),parent int)replicated;
SELECT ID
,NAME
,PARENT
FROM A_ORGAN1 start WITH PARENT = 1
connect BY prior ID = PARENT;
GBase 8a MPP Cluster 最佳实践
5 FAQ
文档版本（2022-02-11）
南大通用数据技术股份有限公司
117
gbase> SELECT ID
-> ,NAME
-> ,PARENT
-> FROM A_ORGAN1 start WITH PARENT = 1
-> connect BY prior ID = PARENT;
+------+-----------------+--------+
| ID
| NAME
| PARENT |
+------+-----------------+--------+
|
2 | 行销部
|
1 |
|
3 | 专业服务组
|
2 |
|
4 | 售后组
|
2 |
+------+-----------------+--------+
3 rows in set (Elapsed: 00:00:00.00)