如何查询SQL层级表中所有带各级父节点的子节点列表

这个需求用递归CTE（公共表表达式）就能轻松搞定，我给你一步步拆解思路和代码：

核心思路 #

我们需要通过递归遍历，从每个有父节点的子节点出发，逐层向上找到所有上级父节点，同时标记出哪个是它的直接父节点。递归CTE分为两部分：锚点查询（获取最基础的直接父子关系）和递归查询（逐层向上追溯父节点）。

具体SQL语句 #

假设你的表名为hierarchy_table，包含ID（节点ID）和parentID（父节点ID，可为空）两个字段，完整查询如下：

WITH recursive_hierarchy AS (
    -- 锚点查询：获取所有有直接父节点的记录，标记直接父节点为1
    SELECT
        ID AS child_id,
        parentID AS parent_id,
        1 AS is_direct_parent
    FROM
        hierarchy_table
    WHERE
        parentID IS NOT NULL  -- 忽略无父节点的顶层记录

    UNION ALL

    -- 递归查询：向上追溯父节点的父节点，标记为非直接父节点（0）
    SELECT
        rh.child_id,
        ht.parentID AS parent_id,
        0 AS is_direct_parent
    FROM
        recursive_hierarchy rh
    JOIN
        hierarchy_table ht ON rh.parent_id = ht.ID
    WHERE
        ht.parentID IS NOT NULL  -- 当父节点是顶层时停止递归
)
SELECT * FROM recursive_hierarchy
ORDER BY child_id, is_direct_parent DESC;  -- 按子节点分组，直接父节点排在前面

代码解释 #

• 锚点部分：筛选出所有parentID不为空的记录，这些是最基础的直接父子关系，所以is_direct_parent设为1。
• 递归部分：把上一轮查询得到的父节点作为新的子节点，去关联它的父节点，此时这些父节点是原节点的间接上级，所以is_direct_parent设为0。当追溯到顶层节点（parentID为空）时，会被WHERE条件过滤，递归自动停止。
• 排序：最后按child_id分组，让直接父节点排在前面，结果更清晰。

示例效果 #

假设你的表数据是：

查询结果会是：

完全符合你的需求：忽略了无父节点的ID=1，每个子节点和所有上级父节点都有对应记录，直接父节点被明确标记。

注意事项 #

如果你的数据存在循环引用（比如A的父是B，B的父是A），需要在递归查询中添加循环检测，或者设置MAXRECURSION参数限制递归层数，避免无限递归。比如在查询末尾加上OPTION (MAXRECURSION 100)（数字可根据你的层级深度调整）。

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