如何在MySQL中汇总和显示游戏列的总计

Question

下面是以下查询的结果：

Select
 MOM_rebels_cms.log.AGE,
 MOM_rebels_cms.log.TEAM,
 MOM_rebels_cms.log.LAST_NAME,
 MOM_rebels_cms.log.NUMBER,
 MOM_rebels_cms.log.Game_1,
 MOM_rebels_cms.log.Game_2,
 MOM_rebels_cms.log.Game_3,
 MOM_rebels_cms.log.Game_4,
 MOM_rebels_cms.log.Game_5
From
 MOM_rebels_cms.log

id | AGE | TEAM | LAST_NAME | NUMBER | GAME_1 | GAME_2 | GAME_3 | GAME_4
 1 |  9  |Rebels|   SMITH   |   22   |    3   |    1   |    2   |    2

我希望在同一行中显示Game_x列的总计，如下所示：

id | AGE | TEAM | LAST_NAME | NUMBER | GAME_1 | GAME_2 | GAME_3 | GAME_4 | TOTAL
 1 |  9  |Rebels|   SMITH   |   22   |    3   |    1   |    2   |    2   |   8

我可以用：

 Select
  MOM_rebels_cms.log.AGE,
  MOM_rebels_cms.log.TEAM,
  MOM_rebels_cms.log.LAST_NAME,
  MOM_rebels_cms.log.NUMBER,
  MOM_rebels_cms.log.Game_1 +
  MOM_rebels_cms.log.Game_2 +
  MOM_rebels_cms.log.Game_3 +
  MOM_rebels_cms.log.Game_4
  AS TOURNEYSum
 From
  MOM_rebels_cms.log

但它只显示：

id | AGE | TEAM | LAST_NAME | NUMBER | TOTAL
 1 |  9  |Rebels|   SMITH   |   22   |   8

我想展示个人比赛的结果以及总数。

Answer 1

您使用游戏的总和，但没有选择他们否则。

 Select
  MOM_rebels_cms.log.AGE,
  MOM_rebels_cms.log.TEAM,
  MOM_rebels_cms.log.LAST_NAME,
  MOM_rebels_cms.log.NUMBER,
  MOM_rebels_cms.log.Game_1,
  MOM_rebels_cms.log.Game_2,
  MOM_rebels_cms.log.Game_3,
  MOM_rebels_cms.log.Game_4,
  MOM_rebels_cms.log.Game_1 +
  MOM_rebels_cms.log.Game_2 +
  MOM_rebels_cms.log.Game_3 +
  MOM_rebels_cms.log.Game_4
  AS TOURNEYSum
 From
  MOM_rebels_cms.log

将单个游戏添加到查询中将运行得很好。

Answer 2

假设您在每一行中有相同数量的游戏，您只需要分别选择列以及您的总数：

Select
  MOM_rebels_cms.log.AGE,
  MOM_rebels_cms.log.TEAM,
  MOM_rebels_cms.log.LAST_NAME,
  MOM_rebels_cms.log.NUMBER,
  MOM_rebels_cms.log.Game_1,
  MOM_rebels_cms.log.Game_2,
  MOM_rebels_cms.log.Game_3,
  MOM_rebels_cms.log.Game_4,
  MOM_rebels_cms.log.Game_1 +
  MOM_rebels_cms.log.Game_2 +
  MOM_rebels_cms.log.Game_3 +
  MOM_rebels_cms.log.Game_4
  AS TOURNEYSum
 From
  MOM_rebels_cms.log

Answer 3

您可以同时选择单个值和和。把它简化一点；

SELECT id, AGE, TEAM, LAST_NAME, NUMBER,
       Game_1,  Game_2,  Game_3,  Game_4,
       Game_1 + Game_2 + Game_3 + Game_4 AS TOTAL
FROM MOM_rebels_cms.log