Ora2pg之树形结构查询转换

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;