mysql - SQL 合并数据并将其转换为列

Question

这是我的数据表的样子:

TeamNum Round   Points1   Points2
1       1       5         21
2       1       10        20
3       1       9         29
1       2       6         22
2       2       11        21
3       2       10        30
1       3       80        50

我还有第二个表:

TeamNum TeamName
1       goteam1
2       goteam2
3       goteam4-1

我希望 SQL 接受它并将其变成这样:

Team    Round1                      Round2            Round3            TeamName
1       (points1+points2 of round1) (same but for r2) (same but for r3) goteam1
2       (points1+points2 of round1) (same but for r2) (same but for r3) goteam2
3       (points1+points2 of round1) (same but for r2) (same but for r3) goteam4-1

上表的输出示例如下:

Team    Round1   Round2    Round3    TeamName
1       26       28        130       goteam1
2       30       32        0         goteam2
3       38       40        0         goteam4-1

实际数据有一堆“points1”和“points2”列，但只有3轮。

我对 SQL 非常陌生，这就是我现在所拥有的:

select 
  `data`.`round`,
  `data`.`teamNumber`,
  sum(`Points1`) + sum(`Points2`) as score
from `data` join `teams` ON `teams`.`teamNumber` = `data`.`teamNumber`
group by `data`.`teamNumber` , `round`
order by `data`.`teamNumber`, `data`.`round`

但它根本不返回任何内容。如果我删除 join 语句，它会显示我想要的所有内容，但不会将 Round1、2 和 3 合并为列，它们都是单独的行。你们能帮我一下吗？谢谢!

Answer 1

使用条件聚合

SELECT t.teamnumber, t.teamname,
       SUM(CASE WHEN d.round = 1 THEN d.points1 + d.points2 ELSE 0 END) round1,
       SUM(CASE WHEN d.round = 2 THEN d.points1 + d.points2 ELSE 0 END) round2,
       SUM(CASE WHEN d.round = 3 THEN d.points1 + d.points2 ELSE 0 END) round3
  FROM data d JOIN teams t
    ON d.teamnumber = t.teamnumber
 GROUP BY t.teamnumber, t.teamname

输出:

| TEAMNUMBER |  TEAMNAME | ROUND1 | ROUND2 | ROUND3 ||------------|-----------|--------|--------|--------||          1 |   goteam1 |     26 |     28 |    130 ||          2 |   goteam2 |     30 |     32 |      0 ||          3 | goteam4-1 |     38 |     40 |      0 |

这里是SQLFiddle 演示