PostgreSQL中如何根据父ID获取自引用表的子级及子子级数据

问题分析 #

你当前的查询仅能获取直接子级，无法递归获取更深层级的子节点（如device_profile_id=5的记录），原因是普通JOIN无法处理层级嵌套的递归关系。PostgreSQL提供了**递归公共表表达式（WITH RECURSIVE）**来处理这类树形结构查询。

解决方案 #

使用以下递归查询语句，可获取指定根节点（device_profile_id=1）的所有层级后代，包括根节点本身：

WITH RECURSIVE device_tree AS (
    -- 锚点成员：选择递归的起始根节点
    SELECT 
        device_profile_id, 
        device_profile_number, 
        device_type, 
        image_index, 
        scale_type, 
        state_index, 
        parent_device_profile_id
    FROM dc_device_profile
    WHERE device_profile_id = 1
    UNION ALL
    -- 递归成员：迭代获取所有子节点，直到没有更深层级
    SELECT 
        d.device_profile_id, 
        d.device_profile_number, 
        d.device_type, 
        d.image_index, 
        d.scale_type, 
        d.state_index, 
        d.parent_device_profile_id
    FROM dc_device_profile d
    INNER JOIN device_tree dt ON d.parent_device_profile_id = dt.device_profile_id
)
SELECT * FROM device_tree;

语句说明 #

1. 锚点成员：定义递归的起始点，这里选中device_profile_id=1的记录作为根节点。
2. 递归成员：通过关联子节点的parent_device_profile_id和当前节点的device_profile_id，逐层获取下一级子节点，直到没有更多子节点为止。
3. 最终查询：从递归生成的临时表device_tree中取出所有数据，即为完整的树形结构数据。

验证结果 #

执行上述语句后，输出将与你期望的结果一致：

1   "AS2"       "FreshPro"  "test"      "FreshPro"  "test"      null
2   "AS4"       "Fresh2222" "test"      "Fresh222"  "test"      1
3   "AS55"      "Fresh122"  "122test"   "Fresh1"    "test11"    1
5   "ASIndis"   "Fresh444"  "4444test"  "Fresh444"  "test222"   3

额外说明 #

• 如果仅需要获取根节点下的后代（不包含根节点自身），只需将锚点成员的条件改为WHERE parent_device_profile_id = 1。
• 该递归查询支持任意深度的层级嵌套，无需手动编写多层JOIN语句。

内容的提问来源于stack exchange，提问作者bharathi