经过测试 , 适用于postgresql , 都是根据父节点的 id 查询其下的子节点
第一种写法
WITH tableName
AS
(
SELECT columnName, id, parent_id FROM tableName parent WHERE parent.id = ? -- parent_id
UNION ALL
SELECT son.columnName, son.id, son.parent_id FROM tableName son
INNER JOIN tableName tree ON son.parent_id = tree.id
)
SELECT * FROM tableName
第二种写法
WITH RECURSIVE tree(id,name,parent_id) AS(
SELECT id,name,parent_id FROM tableName parent WHERE parent.id = ?
UNION ALL
SELECT son.id, son.name, son.parent_id
FROM tableName son
JOIN tree ON son.parent_id = tree.id
)
SELECT id,name,parent_id FROM tree
查询时 , 具体输入 ? 的值即可