SQL父子级查询

SQL子节点

     本文中的所有查询在POSTGRES中查询，其他数据库语法请具体搜索。
     本文的SQL语句适用于具有如下类似结构来记录父子集关系的数据表。以部门表(hr_department)为例进行SQL查询。

   TABLE_NAME(id, parent_id)

id	name	parent_id
1	顶级部门	NULL
2	一层部门	1
3	二层部门-1	2
4	二层部门-2	2

1. 查询某个节点所有的子节点

WITH RECURSIVE p AS (
	SELECT id, parent_id FROM hr_department WHERE id in (2, -1, -1)
	UNION ALL
	SELECT c.id, c.parent_id
	FROM hr_department c JOIN p ON c.parent_id = p.id
)
SELECT id, parent_id FROM p 
--WHERE parent_id is NOT NULL	/*是否包含自身*/

    条件 c.parent_id = p.id 是一直在寻找子表的父节点,直到找到父节点的id=2，递归终止的条件是找到没有父节点的节点，所以这就要求一个部门的父部门不能是自身，一个顶级部门的父节点一定是空的。这就要求我们在设计表的时候加上一个检查约束：

ALTER TABLE hr_department ADD CONSTRAINT chk_self CHECK(parent_id <> id)

     上面的约束是十分合理并且有必要的，因为parent_id 是对自身表id的外键引用，如果我们想清空部门表，在删除了顶级部门的所有子部门后，如果存在自身对自身的引用，那么该条记录怎么都删不掉。

2. 查询所有的叶子节点

SELECT id FROM hr_department_id p
WHERE NOT EXISTS (
	SELECT 1 FROM hr_department c WHERE c.id = p.parent_id
)

     内层查询是在寻找存在以子表为父节点的所有节点，取反，则得到没有以当前节点为父节点的节点，即为所有叶子节点。