postgresql etc功能改写Oracle start with connect by递归功能

目前已做修改的三种例子：
1、start with connect by改写

SELECT
t.*
FROM
pub_organ t,
(
SELECT
organ_id
FROM
(
–原来的写法
– SELECT
– p.organ_id,
– p.organ_type
– FROM
– pub_organ p START WITH p.organ_id = ‘370000’ CONNECT BY PRIOR p.organ_id = p.parent_id

改写成PG的：

	with RECURSIVE t1 as (select 
	
	p.organ_type,
	p.organ_id , p.parent_id
	FROM
	pub_organ p WHERE p.organ_id = '370000'
	UNION
	select 
	t2.organ_type,
	t2.organ_id , t2.parent_id
	FROM
	pub_organ t2,t1
	where 
	t1.organ_id = t2.parent_id)select 
	t1.organ_id,
	t1.organ_type 
	from t1
	) 
WHERE
	organ_type = '1' 
) a 

WHERE
t.parent_id = ‘370000’
AND t.organ_id = a.organ_id
ORDER BY
t.organ_id;

说明：union后使用with recursive的表的字段为原prior跟着的字段
2、level作伪列
源SQL：

SELECT
PRS.ID,
PRS.NAME,
PRS.REMARK,
PRS.PARENT_ID,
(
SELECT
PRES.NAME
FROM
PUB_RESOURCES PRES
WHERE
PRES.ID = PRS.PARENT_ID
) PARENT_ID_NAME,
PRS.RES_TYPE,
PRS.SEQ,
PRS.URL
FROM
(
–元Oracle写法：

– select
– p.id
– from
– PUB_RESOURCES p
– where
– level <> 1 start with
– p.id = ‘3081aae153a848c9b473ccc5c4ddb8bc’ connect by
– prior p.id = p.parent_id

–PG改写：

with RECURSIVE t1 as (select  
p.id, p.parent_id,
 1 as level
  from 
    PUB_RESOURCES p
  where 
     p.id = '3081aae153a848c9b473ccc5c4ddb8bc' 
     union
     select 
     t2.id, t2.parent_id,
     t1.level + 1 as level
   from PUB_RESOURCES t2, t1
 where t2.parent_id = t1.id)
 select id
from t1 where level <> 1

) dd,
PUB_RESOURCES PRS
LEFT JOIN PUB_ROLE_RESOURCE PRR ON
PRS.ID = PRR.RESOURCE_ID
WHERE
PRR.ROLE_ID IN(
SELECT
PUR.ROLE_ID
FROM
PUB_USER_ROLE PUR
LEFT JOIN PUB_ROLES PRO ON
PUR.ROLE_ID = PRO.ROLE_ID
WHERE
PUR.USER_ID = ‘admin’
)
AND PRS.ID = dd.id
AND PRS.RES_TYPE = ‘1’
order by
PRS.Seq

说明：带level，Oracle中与start with一起使用，是一个伪列，进行条件判断，截取层级数据。
PG解决办法：无level列子节引用，可以自定义
模拟level列
3、
ORACLE CONNECT_BY_ROOT,CONNECT_BY_ISLEAF在PG当中的实现
Siblings语法在PG当中的实现
元SQL：
Oracle：

SELECT
	CONNECT_BY_ROOT T.CODE AS ROOT_CODE,
	T.CODE,
	T.CONTENT,
	T.PCODE,
	LEVEL,
	CONNECT_BY_ISLEAF AS IS_SELF 
FROM
	COM_DM T 
WHERE
	T.TYPE = 'ENTTYPE' 
	AND T.FLAG = '1' START WITH T.PCODE = '10000' CONNECT BY PRIOR T.CODE = T.PCODE ORDER SIBLINGS BY T.CODE

修改后：
–黄色是实现的兄弟节点排序（siblings功能）；紫色实现的CONNECT_BY_ROOT功能；蓝色的实现CONNECT_BY_ISLEAF功能

--附SQL
WITH RECURSIVE t1 as (select 
	array[T.CODE]::VARCHAR as sort_path ,
	T.CODE AS ROOT_CODE,
	T.CODE,
	T.CONTENT,
	T.PCODE,
	1 as LEVEL
	FROM
	temp T 
	where T.PCODE = '10000'
	union all
	select 
	(t1.sort_path || array[t2.code])::varchar as sort_path,
	T1.ROOT_CODE AS ROOT_CODE,
	T2.CODE, 
	T2.CONTENT,
	T2.PCODE,
	t1.level + 1 as level
--	CONNECT_BY_ISLEAF AS IS_SELF
	from 
	temp t2,t1
	where 
	t1.CODE = t2.PCODE
	),temp as(SELECT * FROM COM_DM WHERE TYPE = 'ENTTYPE' AND FLAG = '1' )
	select ROOT_CODE,
	CODE,
	CONTENT,
	PCODE,
	level,
	is_LEAF 
	from 
--下面这段查询是为了实现过滤重复数据
		(select DISTINCT
		T2.ROOT_CODE AS ROOT_CODE,
		T2.CODE,
		T2.CONTENT,
		T2.PCODE,
		t2.level,
		--CONNECT_BY_ISLEAF AS IS_SELF
		CASE WHEN T3.CODE IS NULL THEN '1' ELSE '0' END AS is_LEAF
		, t2.sort_path
		FROM t1 T2
			LEFT JOIN t1 T3
	  on	T2.CODE = T3.PCODE)
	order by sort_path

说明：
Oracle中有关键字siblings,与order by连用可以实现递归查询时亲兄弟节点排序
CONNECT_BY_ROOT目的是找到CONNECT BY迭代展开时的根条目，就是找出当前节点的父节点。
CONNECT_BY_ISLEAF目的是指出该条目是否是叶子条目，比如ID为3的，没有以ID=3作为父节点的条目了，所以它就是叶子条目。
解决思路：
PG中无siblings用法，需使用数组排序来实现

root的话就是，源库中是哪个字段就在UNION前边打印出来别名一下，然后在UNION之后还是打印这个值，类似level但是不对值进行处理。

判断是叶子节点：通过关联得到，主要逻辑是不是父节点的那些条目都是叶子，T2.CODE = T3.PCODE，关联不上的那些都是叶子节点。