求助：将AttackTable数据按规则合并至无重复id的Master表

现有AttackTable表结构及数据 #

目标Master表预期结果 #

需求说明 #

• Master表无重复id，需根据id与AttackTable匹配
• 将AttackTable的name依次存入Master的attackName1、attackName2（优先填充第一个字段，已有值则填充第二个）
• 同理将AttackTable的damage存入damage1、damage2

解决方案SQL代码 #

方法1：窗口函数预处理后一次性更新 #

通过窗口函数给每个id下的攻击记录排序，再匹配更新到Master的对应字段：

WITH RankedAttacks AS (
    SELECT 
        id,
        name,
        damage,
        -- 按attackId给每个id下的攻击分配序号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY attackId) AS attackRank
    FROM AttackTable.dbo.AttackTable
)
UPDATE m
SET 
    attackName1 = CASE WHEN ra.attackRank = 1 THEN ra.name ELSE m.attackName1 END,
    attackName2 = CASE WHEN ra.attackRank = 2 THEN ra.name ELSE m.attackName2 END,
    damage1 = CASE WHEN ra.attackRank = 1 THEN ra.damage ELSE m.damage1 END,
    damage2 = CASE WHEN ra.attackRank = 2 THEN ra.damage ELSE m.damage2 END
FROM Master.dbo.Master m
JOIN RankedAttacks ra ON m.id = ra.id;

方法2：分两次更新（逻辑更直观） #

先填充每个id的第一条攻击数据，再填充第二条：

-- 第一次更新：填充attackName1和damage1（每个id的第一条攻击）
UPDATE m
SET 
    attackName1 = at.name,
    damage1 = at.damage
FROM Master.dbo.Master m
JOIN (
    SELECT id, name, damage
    FROM AttackTable.dbo.AttackTable
    WHERE attackId IN (
        SELECT MIN(attackId) 
        FROM AttackTable.dbo.AttackTable 
        GROUP BY id
    )
) at ON m.id = at.id;

-- 第二次更新：填充attackName2和damage2（每个id的第二条攻击）
UPDATE m
SET 
    attackName2 = at.name,
    damage2 = at.damage
FROM Master.dbo.Master m
JOIN (
    SELECT id, name, damage
    FROM AttackTable.dbo.AttackTable
    WHERE attackId IN (
        SELECT MAX(attackId) 
        FROM AttackTable.dbo.AttackTable 
        GROUP BY id
        HAVING COUNT(*) > 1
    )
) at ON m.id = at.id;

说明 #

• 两种方法均假设每个id最多对应2条攻击记录（匹配示例数据场景）
• 方法1的排序依据attackId，保证攻击顺序与原表一致
• 方法2通过取每个id的最小/最大attackId区分第一条和第二条攻击，逻辑更易理解

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