如何在MySQL中根据相邻列匹配实现对应金额列相减？

核心思路 #

利用MySQL的CASE表达式一次性定位'T'和'Z'对应的金额列，直接计算差值，避免多次执行UPDATE语句。

方案1：直接查询结果（无需修改表结构） #

假设你的表名为your_table，包含Article1-Amount1到Article6-Amount6共6组列，执行以下SQL即可直接获取筛选后的记录及差值：

SELECT 
    *,
    -- 提取'T'对应的金额
    CASE
        WHEN Article1 = 'T' THEN Amount1
        WHEN Article2 = 'T' THEN Amount2
        WHEN Article3 = 'T' THEN Amount3
        WHEN Article4 = 'T' THEN Amount4
        WHEN Article5 = 'T' THEN Amount5
        WHEN Article6 = 'T' THEN Amount6
    END AS T_Amount,
    -- 提取'Z'对应的金额
    CASE
        WHEN Article1 = 'Z' THEN Amount1
        WHEN Article2 = 'Z' THEN Amount2
        WHEN Article3 = 'Z' THEN Amount3
        WHEN Article4 = 'Z' THEN Amount4
        WHEN Article5 = 'Z' THEN Amount5
        WHEN Article6 = 'Z' THEN Amount6
    END AS Z_Amount,
    -- 计算差值：T金额 - Z金额
    (CASE
        WHEN Article1 = 'T' THEN Amount1
        WHEN Article2 = 'T' THEN Amount2
        WHEN Article3 = 'T' THEN Amount3
        WHEN Article4 = 'T' THEN Amount4
        WHEN Article5 = 'T' THEN Amount5
        WHEN Article6 = 'T' THEN Amount6
    END) - (CASE
        WHEN Article1 = 'Z' THEN Amount1
        WHEN Article2 = 'Z' THEN Amount2
        WHEN Article3 = 'Z' THEN Amount3
        WHEN Article4 = 'Z' THEN Amount4
        WHEN Article5 = 'Z' THEN Amount5
        WHEN Article6 = 'Z' THEN Amount6
    END) AS Amount_Diff
FROM your_table
WHERE 
    -- 筛选同时包含'Z'和'T'的记录
    (Article1 = 'Z' OR Article2 = 'Z' OR Article3 = 'Z' OR Article4 = 'Z' OR Article5 = 'Z' OR Article6 = 'Z')
    AND
    (Article1 = 'T' OR Article2 = 'T' OR Article3 = 'T' OR Article4 = 'T' OR Article5 = 'T' OR Article6 = 'T');

方案2：将差值存入表中（一次性更新） #

如果需要把差值永久存储到表中，先新增一个存储差值的列，再执行一次UPDATE即可：

-- 1. 新增差值列（仅需执行一次）
ALTER TABLE your_table ADD COLUMN Amount_Diff DECIMAL(10,2);

-- 2. 一次性更新所有符合条件的记录的差值
UPDATE your_table
SET Amount_Diff = 
    (CASE
        WHEN Article1 = 'T' THEN Amount1
        WHEN Article2 = 'T' THEN Amount2
        WHEN Article3 = 'T' THEN Amount3
        WHEN Article4 = 'T' THEN Amount4
        WHEN Article5 = 'T' THEN Amount5
        WHEN Article6 = 'T' THEN Amount6
    END) - (CASE
        WHEN Article1 = 'Z' THEN Amount1
        WHEN Article2 = 'Z' THEN Amount2
        WHEN Article3 = 'Z' THEN Amount3
        WHEN Article4 = 'Z' THEN Amount4
        WHEN Article5 = 'Z' THEN Amount5
        WHEN Article6 = 'Z' THEN Amount6
    END)
WHERE 
    (Article1 = 'Z' OR Article2 = 'Z' OR Article3 = 'Z' OR Article4 = 'Z' OR Article5 = 'Z' OR Article6 = 'Z')
    AND
    (Article1 = 'T' OR Article2 = 'T' OR Article3 = 'T' OR Article4 = 'T' OR Article5 = 'T' OR Article6 = 'T');

方案优势 #

• 逻辑直观：通过CASE表达式逐个匹配Article列，精准定位'T'和'Z'对应的金额，新手易理解
• 效率更高：无需多次执行UPDATE，一次SQL即可完成查询或更新
• 扩展性强：后续新增Article/Amount组时，只需在CASE和WHERE中补充对应列即可

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