資料庫——查詢樹形結構某節點的所有子節點、所有父節點

小海葵發表於2024-05-26
資料庫

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

相關文章