mysql - 想要 GROUP BY 两个相似列中包含的所有值的组合集(而不是两个单独的列)

Question

我有一张包含游戏分数的表格(“分数”)。该表有一列用于 Team1 和一列 Team2(Team1 的对手)以及他们在比赛中的得分。这是一个例子:

------------------------------------------
| id | Team1  | Score1 | Team2  | Score2 |
------------------------------------------
|  1 | Reds   |   1    | Blues  |   6    |
|  2 | Blues  |   5    | Greens |   2    |
|  3 | Greens |   3    | Whites |   4    |
|  4 | Whites |   0    | Greens |   7    |
------------------------------------------

我想要的是一个表(“GameCounts”)，其中包含每支球队参加了多少场比赛，无论该球队是 Team1 还是 Team2。这是我想要的示例:

-----------------------
|  Team  |  GameCount |
-----------------------
|  Reds  |      1     |
|  Blues |      2     |
| Greens |      3     |
| Whites |      2     |
-----------------------

解决这个问题后，我想制定另一个表(“ScoreSum”)，每个团队的列表及其分数的总和:

-----------------------
|  Team  |  ScoreSum  |
-----------------------
|  Reds  |      1     |
-----------------------
|  Blues |     11     |
-----------------------
| Greens |     12     |
-----------------------
| Whites |      4     |
-----------------------

我尝试过的每一个 MySQL 查询都没有完全接近。谢谢!

Answer 1

您可以在一个查询中完成所有这一切，方法是制作一个包含球队和分数的派生表，然后对每个球队名称及其分数的出现次数求和:

SELECT Team, COUNT(*) AS GameCount, SUM(Score) AS ScoreSum
FROM (SELECT Team1 AS Team,
             Score1 AS Score
      FROM Scores
      UNION ALL
      SELECT Team2 AS Team,
             Score2 AS Score
      FROM Scores) S
GROUP BY Team

输出:

Team    GameCount   ScoreSum
Blues   2           11
Greens  3           12
Reds    1           1
Whites  2           4

Demo on dbfiddle