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

前言

多多点赞，会变好看！

多多留言，会变有钱！

一、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

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

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