在SQL Server中计算并比较值以更新Complement列的方法

核心逻辑拆解 #

针对每个Recordnumber分组，先提取对应测试项的值，再比较后更新目标列，分三步执行：

• 步骤1：聚合分组内的目标值
  通过分组查询，把每个Recordnumber下的TW01SS值、TW01D1+TW01D2的和提取出来。用CTE（公共表表达式）可以让逻辑更清晰：

WITH test_agg AS (
    SELECT
        Recordnumber,
        -- 提取TW01SS的值（主键保证唯一）
        MAX(CASE WHEN Test = 'TW01SS' THEN Value END) AS tw01ss_val,
        -- 计算TW01D1和TW01D2的和
        SUM(CASE WHEN Test IN ('TW01D1', 'TW01D2') THEN Value END) AS tw01d_total
    FROM your_table
    GROUP BY Recordnumber
)

• 步骤2：关联原表并更新Complement列
  将聚合结果和原表关联，用GREATEST函数直接取两个值的较大值更新目标列。如果你的数据库不支持GREATEST，用CASE语句替代即可：

-- 支持JOIN的数据库（如MySQL、PostgreSQL）
UPDATE your_table t
JOIN test_agg ta ON t.Recordnumber = ta.Recordnumber
SET t.Complement = GREATEST(ta.tw01ss_val, ta.tw01d_total);

-- 不支持JOIN的UPDATE语法（如SQLite）
UPDATE your_table
SET Complement = (
    SELECT GREATEST(
        (SELECT Value FROM your_table t2 WHERE t2.Recordnumber = your_table.Recordnumber AND t2.Test = 'TW01SS'),
        (SELECT SUM(Value) FROM your_table t3 WHERE t3.Recordnumber = your_table.Recordnumber AND t3.Test IN ('TW01D1', 'TW01D2'))
    )
);

• 步骤3：处理边界场景
  如果存在某个Recordnumber下缺少TW01SS、TW01D1或TW01D2的情况，需要用COALESCE把NULL值转为0，避免计算结果异常：

-- 示例：将NULL转为0后再比较
SET t.Complement = GREATEST(COALESCE(ta.tw01ss_val, 0), COALESCE(ta.tw01d_total, 0));

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