postpreSQL和oracle数据库的递归

oracle:

--包含自身

select *
from sec_org
start with org_id ='9767FA56D52680AEE043C0A8670580AE' --开始节点
connect by prior org_id = parent_org_id --父子关系

 

--不包含自身

select *
from sec_org
start with parent_org_id ='9767FA56D52680AEE043C0A8670580AE' --开始节点
connect by prior org_id = parent_org_id --父子关系

在实际中，可能需要对数据进行过滤，但是在递归查询中，不能进行过滤，可以先对数据进行过滤，使用过滤的数据进行递归

select *
  from (select * from sec_org where status = 'enable')
 start with org_id = '9767FA56D52680AEE043C0A8670580AE' --开始节点
connect by prior org_id = parent_org_id --父子关系

postpreSQL

--包含自身
WITH RECURSIVE cte AS (
	SELECT
		*
	FROM
		sec_org
	WHERE
		org_id = '9767FA56D52680AEE043C0A8670580AE'
	AND status = 'enable'
	UNION
		SELECT
			o.*
		FROM
			sec_org o
		INNER JOIN cte ON o.parent_org_id = cte.org_id
		WHERE
			o.status = 'enable'
) SELECT
	*
FROM
	cte

--不包含自身
WITH RECURSIVE cte AS (
	SELECT
		*
	FROM
		sec_org
	WHERE
		org_id = '9767FA56D52680AEE043C0A8670580AE'
	AND status = 'enable'
	UNION
		SELECT
			o.*
		FROM
			sec_org o
		INNER JOIN cte ON o.parent_org_id = cte.org_id
		WHERE
			o.status = 'enable'
) SELECT
	*
FROM
	cte
WHERE cte.org_id!='9767FA56D52680AEE043C0A8670580AE'

1.首先执行  SELECT * FROM sec_org WHERE org_id = '9767FA56D52680AEE043C0A8670580AE' AND status = 'enable'

2.执行外层的的select * from cte,此时有一条记录

3.执行union后面的语句

4.若3查询出数据，则重复2,3，一直递归，直至3查询的数据为