查询以获取最新的游戏结果

I keep track of certain game results in a MySQL database. Now I want to print the latest results in a nice HTML table. I have three tables:

我在MySQL数据库中跟踪某些游戏结果。现在我想在一个漂亮的HTML表格中打印最新的结果。我有三张桌子:

Table persons contains all participants of the game.

表人包含游戏的所有参与者。

+-----+---------+
| id  |  name   |
+-----+---------+
|  2  | Jon     |
|  3  | Philip  |
|  4  | Tom     |
|  5  | Joey    |
|  6  | Joanna  |
+-----+---------+

The table rounds contains information about each round of the game. Among other things, the week in which the game was fought.

表轮包含有关每轮游戏的信息。除此之外,还有比赛的那一周。

+-----+------+
| id  | week |
+-----+------+
|  1  |    9 |
|  2  |   10 |
|  3  |   11 |
|  4  |   12 |
|  5  |   13 |
+-----+------+

And the table results contains the results for each person and round. The result column is a score in the game.

表格结果包含每个人和每个人的结果。结果列是游戏中的分数。

+------------+----------+--------+
|  personId  | roundId  | result |
+------------+----------+--------+
|         2  |       1  |      2 |
|         4  |       1  |      6 |
|         5  |       1  |      6 |
|         3  |       1  |     10 |
|         2  |       2  |     16 |
|         4  |       2  |     14 |
|         5  |       2  |      5 |
|         3  |       2  |     11 |
+------------+----------+--------+

Now I want to print a table with the players scores each week. I want my output to look like the table below. Note that if a player did not participate one week, the cell should be empty.

现在我想打印一张每周都有球员得分的牌桌。我希望我的输出看起来像下表。请注意,如果玩家未参加一周,则该单元格应为空。

+------+-----+--------+-----+------+--------+
| Week | Jon | Philip | Tom | Joey | Joanna |
+------+-----+--------+-----+------+--------+
|    9 |   2 |     10 |   6 |    6 |        |
|   10 |  16 |     11 |  14 |    5 |        |
+------+-----+--------+-----+------+--------+

So my question is: How do I do to get such output?

所以我的问题是:如何获得这样的输出?

This is not a duplicate. See comments below.

这不是重复的。见下面的评论。

1 个解决方案

#1

So as stated in comments, you want to make a PIVOT, but MySQL does not support it.

因此,如评论中所述,您想要制作PIVOT,但MySQL不支持它。

However since your number of players is low and fixed, you can hardcode the players in a GROUP BY query like this :

但是,由于您的玩家数量很少并且已修复,您可以在GROUP BY查询中对玩家进行硬编码,如下所示:

SELECT  R.Week, 
        SUM(CASE WHEN  P.name = 'Jon'       THEN S.result END) AS Jon,
        SUM(CASE WHEN  P.name = 'Philip'    THEN S.result END) AS Philip,
        SUM(CASE WHEN  P.name = 'Tom'       THEN S.result END) AS Tom,
        SUM(CASE WHEN  P.name = 'Joey'      THEN S.result END) AS Joey, 
        SUM(CASE WHEN  P.name = 'Joanna'    THEN S.result END) AS Joanna                                                     
FROM persons P 
    LEFT JOIN  results S ON P.id=S.personId
    LEFT JOIN rounds R ON R.id=S.roundId
WHERE R.week IS NOT NULL
GROUP BY R.Week

SqlFiddleDemo

#1