MySQL中带不同WHERE条件的多列求和后累加的实现方法

核心实现（通用兼容所有SQL方言） #

你可以通过条件聚合语法，单次查询直接得到最终求和结果，无需执行多次查询再手动累加：

SELECT 
  SUM(
    CASE WHEN designer1 = 'X' THEN duration1 ELSE 0 END +
    CASE WHEN designer2 = 'X' THEN duration2 ELSE 0 END +
    CASE WHEN designer3 = 'X' THEN duration3 ELSE 0 END +
    CASE WHEN designer4 = 'X' THEN duration4 ELSE 0 END
  ) AS total_duration
FROM designerindices
WHERE designerHistSrNumber = 16;

逻辑说明 #

1. 先用WHERE子句筛选出所有designerHistSrNumber = 16的行，缩小计算范围
2. 对每一行分别判断4个designer字段是否等于X：匹配则取对应duration值，不匹配则取0，把四个值相加得到当前行的贡献值
3. 对所有行的贡献值做汇总，得到的结果和你手动累加的18.5完全一致

可选扩展（同时查看分项结果） #

如果需要同时保留四个分项的求和结果，可以用以下查询，一次返回所有明细和总和：

SELECT 
  SUM(CASE WHEN designer1 = 'X' THEN duration1 ELSE 0 END) AS sumdur1,
  SUM(CASE WHEN designer2 = 'X' THEN duration2 ELSE 0 END) AS sumdur2,
  SUM(CASE WHEN designer3 = 'X' THEN duration3 ELSE 0 END) AS sumdur3,
  SUM(CASE WHEN designer4 = 'X' THEN duration4 ELSE 0 END) AS sumdur4,
  SUM(
    CASE WHEN designer1 = 'X' THEN duration1 ELSE 0 END +
    CASE WHEN designer2 = 'X' THEN duration2 ELSE 0 END +
    CASE WHEN designer3 = 'X' THEN duration3 ELSE 0 END +
    CASE WHEN designer4 = 'X' THEN duration4 ELSE 0 END
  ) AS total_duration
FROM designerindices
WHERE designerHistSrNumber = 16;

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