如何递归查询存在替换关联关系的物品数据表？

场景回顾 #

现有物品存储系统，物品有唯一ID，新物品替换旧物品时，在item_reference表记录item_id（新物品ID）与referenced_item_id（被替换旧物品ID）的关联关系。物品分为两类：

• 从未被更新的物品（ID未出现在item_reference.referenced_item_id中）
• 存在1次及以上更新的物品（形成替换链）

数据表结构 #

item表

item_reference表

问题1：已知旧物品ID（如1），获取其最终替换的新物品（如4） #

使用**递归CTE（Common Table Expression）**沿着替换链正向查询，直到找到没有后续替换的物品：

WITH RECURSIVE item_chain AS (
    -- 起始节点：已知的旧物品ID
    SELECT id, message, id AS original_id
    FROM item
    WHERE id = 1
    UNION ALL
    -- 递归步骤：找到当前物品的下一个替换物品
    SELECT i.id, i.message, ic.original_id
    FROM item_chain ic
    JOIN item_reference ir ON ic.id = ir.referenced_item_id
    JOIN item i ON ir.item_id = i.id
)
-- 取链中最后一个节点（即最新的替换物品）
SELECT *
FROM item_chain
WHERE id NOT IN (SELECT referenced_item_id FROM item_reference);

逻辑说明 #

1. 初始CTE查询先定位到起始物品ID=1；
2. 递归环节不断通过item_reference表找到当前物品的后续替换物品；
3. 最终筛选出没有被其他物品引用的节点，即为该链的最新物品。

问题2：给定任意ID，获取所有相关物品（整条替换链的所有节点） #

需要同时查询正向替换链（当前物品的所有后续替换物品）和反向溯源链（当前物品的所有被替换前驱物品），再合并结果：

WITH RECURSIVE full_chain AS (
    -- 起始节点：给定的任意物品ID
    SELECT id, message, 'current' AS position
    FROM item
    WHERE id = 2 -- 这里替换为目标ID
    UNION ALL
    -- 正向递归：找当前节点的后续替换物品
    SELECT i.id, i.message, 'successor' AS position
    FROM full_chain fc
    JOIN item_reference ir ON fc.id = ir.referenced_item_id
    JOIN item i ON ir.item_id = i.id
    UNION ALL
    -- 反向递归：找当前节点的前驱被替换物品
    SELECT i.id, i.message, 'predecessor' AS position
    FROM full_chain fc
    JOIN item_reference ir ON fc.id = ir.item_id
    JOIN item i ON ir.referenced_item_id = i.id
)
-- 去重后返回所有相关物品
SELECT DISTINCT id, message
FROM full_chain
ORDER BY id;

逻辑说明 #

1. 初始CTE定位到目标物品；
2. 正向递归遍历所有替换该物品的后续节点；
3. 反向递归遍历该物品所替换的所有前驱节点；
4. 通过DISTINCT去重（避免递归过程中重复获取节点），最终返回整条链的所有物品。

数据存储优化建议（可选） #

虽然当前格式需保留，但长期来看可做以下优化：

• 在item表新增latest_item_id字段，每次新增替换物品时，更新整条链中所有旧物品的latest_item_id为最新物品ID，后续查询最新物品可直接读取该字段，无需递归；
• 为item_reference表的referenced_item_id和item_id字段建立联合索引，大幅提升递归查询的性能，尤其是数据量较大时。

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