mysql - 从数据库计算玩家统计数据

Question

我有以下表格。

我想从给定的表中获取所有玩家统计记录，包括单个玩家的记录。

1. 玩家姓名
2. 职位
3. 玩的游戏总数
4. 进球数
5. 助攻总数。
6. 总得分(总进球 + 总助攻 = 总得分)。

在尝试之后我想到了这个问题

SELECT SQL_CALC_FOUND_ROWS
    CONCAT(u.first_name, ' ', u.last_name) as player_name,
    p.position, 
    COUNT(g.id)
FROM 
    gce_player p 
LEFT JOIN 
    gce_user u ON(u.id = p.user_id) 
LEFT JOIN 
    gce_game_team_lineup gtl ON(gtl.player_id = p.id) 
LEFT JOIN 
    gce_game_team gt ON(gt.id = gtl.game_team_id) 
LEFT JOIN 
    gce_game_goal gg ON(gg.player_id = p.id) 
LEFT JOIN 
    gce_game g ON(g.id = gt.game_id) 
GROUP BY p.id 
ORDER BY p.id asc

上面的查询返回我正确的记录，直到玩过的游戏总数，我在这之后获取正确的记录时遇到问题，非常感谢任何类型的帮助。

这里是 sqlfiddle 的链接如果您想查看架构，我也添加了一些测试数据。

谢谢。

更新:

here are few of the rules to remember.

1. Number of goals scored = total number of goals scored by a player. for example if in gce_game_goal table there are 10 rows which have the value of player_id as 4 it means the player have scored 10 goals and i need to fetch this record for individual player, and likewise if there are 7 rows in which player_id have value of 3 this means player with id 3 have scored 7 goals and likewise.

2. Total number of assist for goals = total number of assist given to a goalie by a player (assist is like a pass in football). i need to calculate total number of assist or pass that was done by a user.
   for each goal there will be two assist, and each assist are players who pass the ball to a golaie. i want to count the number of passes or assist given by a player. for example if in gce_game_goal table there are 8 rows or records that have the value of 3 in either assis1_id or assist2_id column, this means player with id 3 have scored 8 assist in total

.

如果您还有任何疑问/问题，请告诉我，我会尝试改进我的问题

谢谢

Answer 1

您面临的问题是由多个不同维度的数据(比如按比赛和按目标)聚合引起的。这会导致每个玩家的叉积。

一个相当通用的解决方案是在 from 子句中沿每个维度进行聚合。每个变量(或者可能是几个变量)来自不同的聚合:

select u.last_name, u.first_name, p.position, 
       pg.goals, pg.assists, (pg.goals + pg.assists) as TotalPoints
from gce_player p join
     gce_user u
     on p.user_id = u.id left outer join
     (select player_id, SUM(goal) as goals, SUM(assist) as assists
      from ((select player_id, 1 as goal, 0 as assist
             from gce_game_goal
            ) union all
            (select assist1_id, 0 as goal, 1 as assist
             from gce_game_goal
            ) union all
            (select assist2_id, 0 as goal, 1 as assist
             from gce_game_goal
            )
           ) t
      group by player_id
     ) pg
     on pg.player_id = p.id left outer join
     (select gtl.player_id, count(*) as NumTeams
      from gce_game_team_lineup gtl join
           gce_game_team gt
           on gtl.id = gt.team_id
     ) g
     on g.player_id = p.id