构建最优SQL查询获取多产品迁移记录中的最新产品版本

我们有一张记录产品迁移关系的表，每条数据代表一个产品向另一个产品的迁移路径：

需求很明确：输入任意一个产品名称（比如PROD1或PROD3），查询出它经过所有迁移步骤后最终的目标产品（比如上述例子中所有前置产品最终都指向PROD5）。

最优实现：递归CTE（公共表达式） #

递归CTE是处理这类层级遍历问题的最优方案之一，逻辑清晰且效率较高，能一次性遍历完整的迁移链。假设表名为product_migrations，SQL代码如下：

WITH RECURSIVE migration_chain AS (
    -- 锚点查询：定位输入产品的直接迁移目标
    SELECT 
        "Product From" AS current_product,
        "Product To" AS next_product
    FROM product_migrations
    WHERE "Product From" = 'PROD1' -- 替换为你要查询的产品名
    
    UNION ALL
    
    -- 递归遍历：不断跟进后续的迁移路径
    SELECT 
        mc.next_product AS current_product,
        pm."Product To" AS next_product
    FROM migration_chain mc
    JOIN product_migrations pm ON mc.next_product = pm."Product From"
)
-- 筛选出没有后续迁移的节点，即为最终版本
SELECT next_product AS latest_product
FROM migration_chain
WHERE next_product NOT IN (SELECT "Product From" FROM product_migrations);

代码说明 #

1. 锚点部分：先找到输入产品的第一个迁移目标，作为遍历的起点。
2. 递归部分：将上一步得到的迁移目标作为新的起始产品，继续查找下一个迁移目标，直到没有后续迁移记录为止。
3. 最终筛选：通过判断next_product不在Product From列中，确定这是迁移链的终点，也就是最新的产品版本。

防循环优化（可选） #

如果你的数据存在潜在的循环迁移风险（比如PROD5又迁回PROD1），可以在递归CTE中加入路径追踪，避免死循环：

WITH RECURSIVE migration_chain AS (
    SELECT 
        "Product From" AS current_product,
        "Product To" AS next_product,
        ARRAY["Product From"] AS path -- 记录已遍历的产品路径
    FROM product_migrations
    WHERE "Product From" = 'PROD1'
    
    UNION ALL
    
    SELECT 
        mc.next_product AS current_product,
        pm."Product To" AS next_product,
        mc.path || pm."Product From" AS path
    FROM migration_chain mc
    JOIN product_migrations pm ON mc.next_product = pm."Product From"
    WHERE pm."Product From" <> ALL(mc.path) -- 跳过已在路径中的产品，防止循环
)
SELECT next_product AS latest_product
FROM migration_chain
WHERE next_product NOT IN (SELECT "Product From" FROM product_migrations);

通用参数化查询 #

如果需要做成可复用的查询，不同数据库的参数写法略有差异：

• PostgreSQL：用$1替换固定值，比如WHERE "Product From" = $1
• MySQL：用?作为占位符，比如WHERE "Product From" = ?
• SQL Server：用@product作为参数，比如WHERE "Product From" = @product

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