Oracle树形结构查询转Postgresql总结
一、基本语法
--prior在子id一侧
select * from table [start with condition1]
connect by [prior] id=parentid
一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。
start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。
connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。
--prior在父id一侧
select * from table [start with condition1]
connect by id= [prior] parentid
这种用法就表示从下往上查找数据，可以理解为从叶子节点往上查找父级几点，用第一层数据的parentid去跟表记录里面的id进行匹配，匹配成功那么查找出来的就是第二层数据；上面的那种就是从父级节点往下查找叶子节点。
二、LEVEL
level关键字，代表树形结构中的层级编号；第一层是数字1，第二层数字2，依次递增。
SELECT 1 as con_1,'2' as con_2,tt.emp_id,lead_id,emp_name,salary,level,to_char(salary,'999')
FROM emp tt where level>1 START WITH lead_id = 0 connect BY prior tt.emp_id = lead_id
三、CONNECT_BY_ROOT方法
CONNECT_BY_ROOT方法，能够获取第一层集结点结果集中的任意字段的值；例CONNECT_BY_ROOT（字段名）。
select t.*, level, CONNECT_BY_ROOT(emp_id)
from emp t
start with t.emp_id = 1
connect by prior t.emp_id = t.lead_id;
四、CONNECT_BY_ISCYCLE、NOCYCLE
CONNECT_BY_ISCYCLE伪列，如果当前行有一个子行，且子行又是当前行的祖先行，CONNECT_BY_ISCYCLE返回1，否则返回0. 只有在CONNECT BY从句中指定了NOCYCLE参数，才能指定CONNECT_BY_ISCYCLE。由于CONNECT BY存在循环数据，NOCYCLE能使Oracle返回查询结果，否则将查询失败。
SELECT 1 as con_1,'2' as con_2,tt.emp_id,lead_id,emp_name,salary,CONNECT_BY_ISCYCLE
FROM emp tt where level>1 START WITH lead_id = 0 connect BY NOCYCLE prior tt.emp_id = lead_id;
五、CONNECT_BY_ISLEAF
CONNECT_BY_ISLEAF表示是不是叶子节点。
SELECT 1 as con_1,'2' as con_2,tt.emp_id,lead_id,emp_name,salary,level,CONNECT_BY_ISLEAF
FROM emp tt where level>1 START WITH lead_id = 0 connect BY prior tt.emp_id = lead_id
六、SYS_CONNECT_BY_PATH
SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。
select SYS_CONNECT_BY_PATH(emp_name, '>') AS chain
from emp
start with emp_id =1
connect by lead_id = prior emp_id;
七、ORDER SIBLINGS BY表达式
在层次查询中如需对亲兄弟的先后顺序进行限定，就必须要使用特有的“ORDER SIBLINGS BY”
select emp_id,emp_name, SYS_CONNECT_BY_PATH(emp_id, '>') AS chain
from emp
start with emp_id =1
connect by lead_id = prior emp_id
order siblings by emp_id desc;
八、转postgresql
转换SYS_CONNECT_BY_PATH函数
--oracle
SELECT emp_id,lead_id,emp_name,SYS_CONNECT_BY_PATH (emp_name,'/') PATH
FROM emp
START WITH (emp_id = 1)
CONNECT BY (PRIOR emp_id = lead_id);
--pg
with recursive cte(emp_id, lead_id, emp_name, path) as
(
select emp_id,
lead_id,
emp_name,
'/' || emp_name
from emp e
where emp_id = 1
union all
select c.emp_id,
c.lead_id,
c.emp_name,
concat_ws('/', p.path, c.emp_name)
from emp c
join cte p on p.emp_id = c.lead_id
) select e.* FROM cte e;
转换CONNECT_BY_ISLEAF
--oracle
SELECT emp_id,lead_id,emp_name,CONNECT_BY_ISLEAF ISLEAF
FROM emp
START WITH (emp_id = 1)
CONNECT BY (PRIOR emp_id = lead_id);
--pg
with recursive cte(emp_id, lead_id, emp_name, visited) as
(
select emp_id,
lead_id,
emp_name,
array[emp_id] as visited
from emp e
where emp_id = 1
union all
select c.emp_id,
c.lead_id,
c.emp_name,
p.visited || c.emp_id
from emp c
join cte p on p.emp_id = c.lead_id
where c.emp_id <> all(p.visited)
) select emp_id,lead_id,emp_name,
not exists (select * from cte pr where pr.lead_id = e.emp_id) as is_leaf FROM cte e;
--pg
WITH RECURSIVE Tab_HH__1 AS (
SELECT emp_id,lead_id,emp_name, array[emp_id] as visited FROM EMP WHERE (emp_id::numeric=1)
UNION ALL
SELECT Tab_HH__2.emp_id,Tab_HH__2.lead_id,Tab_HH__2.emp_name, Tab_HH__1.visited || Tab_HH__2.emp_id as visited FROM (
SELECT emp_id,lead_id,emp_name FROM EMP
) Tab_HH__2 INNER JOIN Tab_HH__1 ON (Tab_HH__1.emp_id=Tab_HH__2.lead_id) where Tab_HH__2.emp_id <> all(Tab_HH__1.visited)
)
SELECT emp_id,lead_id,emp_name,(case when not exists (select * from Tab_HH__1 p where p.lead_id = Tab_HH__1.emp_id) then 1 else 0 end )as is_leaf FROM Tab_HH__1
--pg
WITH RECURSIVE Tab_HH__1 AS (
SELECT emp_id,lead_id,emp_name, array[emp_id] as visited FROM EMP WHERE (emp_id::numeric=1)
UNION ALL
SELECT Tab_HH__2.emp_id,Tab_HH__2.lead_id,Tab_HH__2.emp_name, Tab_HH__1.visited || Tab_HH__2.emp_id as visited FROM (
SELECT emp_id,lead_id,emp_name FROM EMP
) Tab_HH__2 INNER JOIN Tab_HH__1 ON (Tab_HH__1.emp_id=Tab_HH__2.lead_id) where Tab_HH__2.emp_id <> all(Tab_HH__1.visited)
)
SELECT emp_id,lead_id,emp_name,
(case when not exists (select * from Tab_HH__1 p where p.lead_id = Tab_HH__1.emp_id) then 1 else 0 end ) as is_leaf ,
(case when not exists (select * from Tab_HH__1 p where p.lead_id = Tab_HH__1.emp_id) then 1 else 0 end )||'-'||emp_name as aaaaa
FROM Tab_HH__1 order by is_leaf
转换CONNECT_BY_ROOT
--oracle
SELECT emp_id,lead_id,emp_name,CONNECT_BY_ROOT(emp_id) as root_id
FROM emp
START WITH (emp_id = 1)
CONNECT BY (PRIOR emp_id = lead_id);
--pg
with recursive cte(emp_id, lead_id, emp_name, root_id) as
(
select emp_id,
lead_id,
emp_name,
emp_id as root_id
from emp e
where emp_id = 1
union all
select c.emp_id,
c.lead_id,
c.emp_name,
p.root_id
from emp c
join cte p on p.emp_id = c.lead_id
) select emp_id,lead_id,emp_name, root_id FROM cte e;
转换NOCYCLE
--oracle
SELECT emp_id,lead_id,emp_name
FROM emp
START WITH (emp_id = 1)
CONNECT BY NOCYCLE (PRIOR emp_id = lead_id);
--pg
with recursive cte(emp_id, lead_id, emp_name, visited) as
(
select emp_id,
lead_id,
emp_name,
array[emp_id] as visited
from emp e
where emp_id = 1
union all
select c.emp_id,
c.lead_id,
c.emp_name,
p.visited || c.emp_id as visited
from emp c
join cte p on p.emp_id = c.lead_id
where c.emp_id <> all(p.visited)
) select emp_id,lead_id,emp_name FROM cte e;
转换CONNECT_BY_ISCYCLE
--oracle
SELECT emp_id,lead_id,emp_name,CONNECT_BY_ISCYCLE as cycle
FROM emp
START WITH (emp_id = 1)
CONNECT BY NOCYCLE (PRIOR emp_id = lead_id);
--pg
with recursive cte(emp_id, lead_id, emp_name, visited,cycle) as
(
select emp_id,
lead_id,
emp_name,
array[emp_id] as visited,
false as cycle -- 是否循环(初始为否)
from emp e
where emp_id = 1
union all
select c.emp_id,
c.lead_id,
c.emp_name,
p.visited || c.emp_id as visited,
(c.emp_id = ANY(p.visited || c.emp_id)) as cycle -- 是否循环，判断新点是否已经在之前的路径中
from emp c
join cte p on p.emp_id = c.lead_id
where c.emp_id <> all(p.visited) --nocycle
) select emp_id,lead_id,emp_name,cycle FROM cte e;
