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
)
内层查询是在寻找存在以子表为父节点的所有节点,取反,则得到没有以当前节点为父节点的节点,即为所有叶子节点。