在某些业务场景中,DBA大佬会设计一些比如权限管理,地区之类的表,这些表要想查看关系,在MySQL中可以使用自连接查看,而在Oracle或者PostgreSQL就需要使用递归查询
Oracle:
SELECT * TABLE_NAME
START WITH ID = 1
CONNECT BY PRIOR ID = PARENT_ID;
PostgreSQL:
WITH RECURSIVE A AS(
SELECT 1 AS ID, PARENT_ID, TITLE
FROM TABLE_NAME
WHERE ID = 1 --(此处WHERE条件代表递归查询的开始条件)
UNION ALL
SELECT A.ID + 1, B.PARENT_ID, B.TITLE
FROM TABLE_NAME AS B
JOIN A ON A.ID = B.PARENT_ID
)
SELECT ID,PARENT_ID,TITLE FROM A;