MySQL多表JOIN跨多竞赛场景下游戏重复统计问题咨询

问题根因 #

当前查询链路为gamedata → games → roundgames → rounds → competitions，因单场赛事可关联多轮次、多竞赛，同个gameId会在结果集生成多行重复记录。所有直接对行值做的聚合统计（如普通COUNT、SUM）会将同一场赛事的计数/累加值放大，最终统计结果失真。

可落地的修正方案 #

• 对赛事属性类的计数场景，将去重逻辑嵌入统计表达式，避免普通聚合的重复计数
  原写法COUNT(CASE WHEN g.isTestMatch = 1 THEN 1 ELSE 0 END) as TTLTests、以及新增的SUM(CASE WHEN g.isRanfurly = 1 THEN 1 ELSE 0 END) as TTLTests，都会把重复行的属性值计入统计。这类需要判断赛事属性（是否为测试赛、是否为Ranfurly盾赛事）的计数，统一替换为按gameId去重的统计逻辑：

  -- 测试赛计数：只要gameId满足属性条件，无论重复关联多少行，只计1次
  COUNT(DISTINCT CASE WHEN g.isTestMatch = 1 THEN g.gameId END) as TTLTests
  -- Ranfurly盾赛事计数
  COUNT(DISTINCT CASE WHEN g.isRanfurly = 1 THEN g.gameId END) as TTLRanfurly
  

  原理是COUNT(DISTINCT)会对满足条件的gameId做唯一性校验，哪怕同个game因关联多竞赛出现在结果集多行，也只会被统计1次，从聚合逻辑层面消除多表关联的重复影响。
• 对单场固定值的累加场景，同样需要做去重避免重复累加
  原写法SUM(gd.tries) as TTLTries存在同样的问题：单场球员的达阵数是固定值，同个game因多竞赛关联出多行后，直接SUM会把同一个达阵数重复累加N次。可采用拼接唯一标识的方式做去重累加：

  -- 将单场tries值与gameId拼接为唯一值，SUM后取低位还原实际累加值
  SUM(DISTINCT gd.tries * 100000 + gd.gameId) % 100000 as TTLTries
  

  这种写法适配单game唯一记录的场景，不会因多竞赛关联出现重复累加。
• 修正原SQL的语法矛盾
  原查询在分组场景下写了SELECT DISTINCT(g.gameId)搭配GROUP BY t.teamName，不符合SQL分组规则（分组后单组包含多个gameId，SELECT单字段无实际意义）。需要将SELECT列表的非聚合字段完全匹配GROUP BY维度，避免在多数SQL引擎上执行报错。

修正后的参考SQL片段 #

SELECT 
  t.teamName, 
  COUNT(DISTINCT gd.gameId) as Appearances,
  COUNT(DISTINCT CASE WHEN g.isTestMatch = 1 THEN g.gameId END) as TTLTests,
  COUNT(DISTINCT CASE WHEN g.isRanfurly = 1 THEN g.gameId END) as TTLRanfurly,
  SUM(DISTINCT gd.tries * 100000 + gd.gameId) % 100000 as TTLTries,
  IF(gd.homeaway = 1, g.team1Id, g.team2Id) as myteamId
FROM `gamedata` gd
JOIN `games` g ON gd.gameId = g.gameId
JOIN `teams` t ON t.teamId = IF(gd.homeaway = 1, g.team1Id, g.team2Id)
JOIN `roundgames` rg ON rg.gameId = gd.gameId
JOIN `rounds` r ON r.roundId = rg.roundId
JOIN `competitions` c ON r.competitionId = c.competitionId
WHERE `playerId` = 1 AND `didntPlay` = 0 AND t.teamType = 2
GROUP BY t.teamName, myteamId

内容的提问来源于stack exchange，提问作者Jason