如何通过资产ID查询所有递归关联的历史资产？

背景说明 #

现有两张业务表：

1. Assets表（存储资产基础信息）：

2. AssetConversion表（记录资产转换关系，asset_id_in是转换前资产，asset_id_out是转换后资产）：

资产转换形成链式关联：

• 1 → 2 → 3 → 4
• 5 → 6

需求：给定一个资产ID，查询其所有历史关联资产（含自身），并按目标资产→上游源头资产的顺序返回（比如输入ID4，需返回4、3、2、1）。

解决方案 #

使用**递归CTE（公共表表达式）**可以高效遍历反向关联的资产链，以下是适配MySQL 8.0+、PostgreSQL、SQL Server等主流关系型数据库的查询语句：

WITH RECURSIVE AssetHistory AS (
    -- 锚点：先获取目标资产自身
    SELECT 
        a.id, 
        a.name,
        0 AS depth  -- 用于排序，目标资产深度为0，上游资产深度递增
    FROM Assets a
    WHERE a.id = 4  -- 替换为实际查询的资产ID

    UNION ALL

    -- 递归：反向追溯上游关联资产
    SELECT 
        a.id, 
        a.name,
        ah.depth + 1 AS depth
    FROM AssetHistory ah
    JOIN AssetConversion ac 
        ON ah.id = ac.asset_id_out  -- 反向关联：用当前资产找它的转换前身
    JOIN Assets a 
        ON ac.asset_id_in = a.id
)
-- 按深度升序排序，保证目标资产在前，上游资产依次跟进
SELECT id, name
FROM AssetHistory
ORDER BY depth ASC;

结果示例 #

输入资产ID为4时，执行查询返回结果：

补充说明 #

• 递归CTE分为锚点和递归两部分：锚点锁定目标资产，递归成员不断通过转换表反向关联，直到没有上游资产为止。
• 若需要展示转换的额外数据，可在查询字段中加入ac.additional_data。

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