mysql - 如何从 MySQL 表中的两个不同列中获取总和

Question

我在名为“match_scores”的表中有以下列:

player_1, player_2, player_1_points, player_2_points
tom, mike, 110, 40
tom, joe, 115, 30
mike, tom, 120, 30

我希望结果是:

tom has 255 points
mike has 160 points
joe has 30 points

Answer 1

您可以“反透视”数据，然后重新聚合。在 SQL 中，这看起来像:

select player, sum(points) as points
from ((select player_1 as player, player_1_points as points from match_scores ms1) union all
      (select player_2 as player, player_2_points as points from match_scores ms2) 
     ) t
group by player;

这会将数据作为包含两列的结果集返回。您可以在 SQL 中转换为字符串，但我强烈建议您在应用层执行此操作。