Mysql 树形表连接和行数统计

Question

我有下面这三个表。我想使用 MySQL 查询加入他们。在我提供的表格下方还提供了我的输出应该是什么样子。

游戏表:

GameID  GameName    
1       NvsA
2       NvsB
3       DvsA
4       NvsE

PlayerOnGame:

GameID  PlayerName  PlayerNumber  PlayerID
1       Clement     7             10
1       Niyoyita    8             11
2       Clement     8             10
2       David       6             5

玩家数据表:

GameID  Action  PlayerNumber
1       kick    7
1       pass    8
1       Run     7
1       Kick    7
2       Kick    8
2       Pass    6
2       Run     8
2       Run     8

仅PlayerName = Clement的输出:

GameName  Action  ActionCount
NvsA      Kick    2
NvsA      Run     1
NvsB      Kick    1
NvsB      Run     2

请问有人可以帮我查询以获得此输出吗？

Answer 1

您可以使用此查询来计数

SELECT
    gt.GameName,
    pdt.Action,
    count(pdt.Action) AS ActionCount
FROM GameTable AS gt
INNER JOIN PlayerdataTable AS pdt ON pdt.GameID = gt.GameID
INNER JOIN PlayerOnGame AS pg ON pg.GameID = pdt.GameID AND pg.PlayerNumber = pdt.PlayerNumber
WHERE pg.PlayerName = 'Clement'
GROUP BY gt.GameID , pdt.Action