递归查询中parentid字段意外返回空值的问题排查

问题描述 #

你拥有两张数据表：

persons:
- personid: int not null
- personname: varchar(200) not null

parents:
- parentid: int not null
- personid: int not null

其中parents表每行记录一对父/子关系，一人可拥有多个子女。你编写了递归查询期望获取指定人员及其所有子女的数据，但结果中除首行parentid为NULL符合预期外，其余行的parentid均为空，仅personid正确。

错误原因 #

你的递归查询在父ID的取值逻辑上存在问题：

• 递归部分的SELECT语句中，你错误地取了p.parentid作为CTE的parentid字段，但实际上，当通过p.parentid = cte.personid关联时，cte.personid才是当前子女的父ID（即起始人员或其后代的ID）。
• 虽然理论上p.parentid等于cte.personid，但在递归查询的字段映射中，直接引用cte.personid才能确保父ID被正确传递，你的写法因字段关联逻辑的偏差导致值未被正确赋值。

修正后的递归查询 #

将递归部分的parentid字段改为引用cte.personid，确保父ID被正确传递：

WITH RECURSIVE cte (personid, parentid) as (
    -- Anchor query：起始人员，父ID为NULL
    SELECT
        personid AS personid,
        NULL AS parentid
    FROM
        persons
    WHERE
        personid = ?
    UNION ALL
    -- Recursive query：查询当前人员的所有子女
    SELECT
        p.personid,  -- 子女的personid
        cte.personid -- 子女的父ID（即当前CTE中的人员ID）
    FROM
        parents p
    INNER JOIN
        cte ON p.parentid = cte.personid
)
SELECT
    parentid,
    personid
FROM
    cte

说明 #

修正后，递归查询会正确传递父ID：

• 锚点行：起始人员的personid，parentid为NULL。
• 递归行：每个子女的personid，对应的parentid为其父辈的personid（即CTE中上一层的personid）。

这样查询结果中的parentid字段就会正确显示每个子女的父ID，不再为空。

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