如何用递归SQL查询获取层级表中底层元素的顶级父ID？

完全可以通过递归SQL实现这个需求，核心思路是先定位没有子节点的底层元素，再通过递归向上追溯，直到找到PARENTID等于自身的顶级父节点。

适用场景 #

支持CTE递归语法的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11gR2+等）

完整SQL代码 #

假设你的表名为your_table，代码如下：

WITH RECURSIVE hierarchy AS (
    -- 锚点：筛选所有底层节点（无任何子节点的记录）
    SELECT 
        ID, 
        PARENTID,
        PARENTID AS CurrentParent
    FROM your_table
    WHERE NOT EXISTS (
        SELECT 1 FROM your_table t2 
        WHERE t2.PARENTID = your_table.ID
    )
    UNION ALL
    -- 递归：向上追溯父节点，直到到达顶级节点
    SELECT 
        h.ID, 
        t.PARENTID,
        t.PARENTID AS CurrentParent
    FROM hierarchy h
    JOIN your_table t ON h.CurrentParent = t.ID
    WHERE t.PARENTID != t.ID -- 未到顶级时继续递归
)
-- 提取最终的顶级父节点记录
SELECT 
    ID, 
    CurrentParent AS UltimateParentID
FROM hierarchy
WHERE CurrentParent = ID
ORDER BY ID;

代码逻辑说明 #

1. 锚点成员：通过NOT EXISTS筛选出所有底层节点——也就是没有其他记录把它当作父节点的元素。
2. 递归成员：将每个节点的当前父节点替换为其父节点的父节点，循环这个过程，直到父节点的PARENTID等于自身（即顶级节点）。
3. 最终筛选：从递归结果中挑出已经到达顶级节点的记录，得到每个底层元素对应的顶级父ID。

示例验证 #

将示例表数据代入后，运行结果会和你期望的完全一致：

ID    UltimateParentID               
----- ----------------  
5     1
7     6
8     6
9     1
10    10

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