数据库——查询树形结构某节点的所有子节点、所有父节点

一、ORACLE
查询当前节点与所有子节点：

SELECT * FROM YW_XYZB CONNECT BY PRIOR ID = PARENT_ID START WITH ID = '***'

查询当前节点与所有父节点：

SELECT * FROM YW_XYZB CONNECT BY PRIOR PARENT_ID = ID START WITH ID = '***'

二、PgSQL
查询当前节点与所有子节点：

WITH RECURSIVE temp AS (
	SELECT n.gfbm, n.fjbm FROM dic_gfbm_info AS n WHERE n.gfbm = '01'
	UNION ALL 
	SELECT r.gfbm, temp.gfbm FROM temp JOIN dic_gfbm_info AS r ON r.fjbm = temp.gfbm
) SELECT * FROM temp

根据子节点查找所有节点并且拼接路径，a>b>c

(WITH RECURSIVE tempx AS (
				SELECT n.resource_id ,n.father_id,n.resource_zh ::varchar(150)  FROM t_sys_resources AS n WHERE n.resource_id = b.resource_id
				UNION ALL
				SELECT r.resource_id ,r.father_id,(CONCAT(r.resource_zh ,'>',tempx.resource_zh))::varchar(150)  FROM tempx JOIN t_sys_resources AS r ON tempx.father_id = r.resource_id
			) SELECT resource_zh FROM tempx  where resource_id='') as menuPosition

查询当前节点与所有父节点：

WITH RECURSIVE temp AS (
	SELECT n.gfbm, n.fjbm FROM dic_gfbm_info AS n WHERE n.gfbm = '00202'
	UNION ALL
	SELECT r.gfbm, r.fjbm FROM temp JOIN dic_gfbm_info AS r ON temp.fjbm = r.gfbm
) SELECT * FROM temp

注：来源于 https://blog.csdn.net/qq_26230421/article/details/121097105
参考：https://blog.csdn.net/triplestudio/article/details/89374184