MySQL如何在不改动原有SELECT语句前提下按条件对bia字段求和

实现方案 #

直接使用MySQL内置的IF()函数即可实现你要的条件求和逻辑，无需修改原有查询的其他部分，完整写法如下：

SELECT 
  possession_team AS Team, 
  ball_carrier_receiver AS Player,
  sum(gain) - sum(bia) AS `TOT YAC`,
  sum(IF(series_end = 'Touchdown', bia, 0)) AS `BIA TD`
FROM plays 
WHERE run_pass='P' AND pass_result <>'S' AND pass_result <>'R'
GROUP BY possession_team, ball_carrier_receiver;

逻辑说明 #

• IF(series_end = 'Touchdown', bia, 0) 逐行判断条件：满足条件时返回该行bia的真实取值，不满足时返回0，求和时0不会影响最终结果
• 如果你偏好CASE WHEN写法也可以用下面的写法，同样返回bia真实值求和，不是计数：

  SUM(CASE WHEN series_end = 'Touchdown' THEN bia ELSE 0 END) AS `BIA TD`
  

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