SQL Server 2016中如何构建ItemID到TerminalItemID的映射表？

要实现每个ItemID到最终TerminalItemID的映射，我们可以利用**递归CTE（公共表表达式）**来遍历链式层级结构：

• 从终端项开始，先将每个终端项映射到自身（满足业务要求）。
• 递归向上追溯每个终端项的所有祖先节点，将每个祖先节点关联到对应的终端项。

-- 声明临时表@Mapping
DECLARE @Mapping TABLE (ItemID INT, TerminalItemID INT)

-- 使用递归CTE遍历层级结构，生成映射关系
;WITH RecursiveItemMapping AS (
    -- 锚点成员：终端项映射自身
    SELECT 
        ti.TerminalItemID AS ItemID,
        ti.TerminalItemID AS TerminalItemID
    FROM @TerminalItems ti
    
    UNION ALL
    
    -- 递归成员：向上查找当前项的父节点，继承相同的终端项映射
    SELECT 
        i.OriginalItem AS ItemID,
        rim.TerminalItemID AS TerminalItemID
    FROM dbo.Item i
    INNER JOIN RecursiveItemMapping rim 
        ON i.ItemID = rim.ItemID
    WHERE i.OriginalItem IS NOT NULL -- 无父节点时停止递归
)

-- 将映射结果插入@Mapping临时表
INSERT INTO @Mapping (ItemID, TerminalItemID)
SELECT ItemID, TerminalItemID
FROM RecursiveItemMapping
ORDER BY ItemID; -- 可选排序，根据实际需求调整

-- 验证结果
SELECT * FROM @Mapping;

关键说明 #

• 锚点成员：直接从@TerminalItems获取所有终端项，建立自身到自身的映射，符合业务逻辑要求。
• 递归成员：通过关联dbo.Item表的ItemID（子节点）与递归CTE中的ItemID，找到子节点对应的父节点（OriginalItem），并将父节点映射到同一终端项。
• 终止条件：当OriginalItem为NULL时，说明已到达层级根节点，停止递归。
• 结果验证：最后可通过查询@Mapping确认所有ItemID的终端映射关系是否符合预期。

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