mysql - 内连接产生的重复数据

Question

我有 3 个表，我正在尝试使用内部联接来联接，但由于某种原因，它们导致了一堆重复的结果。我一直在抓狂地试图找出这里出了什么问题。下面是我正在使用的表格:

 Table:gameday.atbats

   GameName                     Inning num  b   s   o   Batter   Pitcher      Result
-----------------------------------------------------------------------------------------
    gid_2008_09_24_cinmlb_houmlb_1  1   1   2   3   1   457803  150116  Jay Bruce strikes out swinging.  
    gid_2008_09_24_cinmlb_houmlb_1  1   2   1   0   2   433898  150116  Jeff Keppinger lines out to right fielder Hunter Pence.  
    gid_2008_09_24_cinmlb_houmlb_1  1   3   3   1   2   458015  150116  Joey Votto singles on a line drive to right fielder Hunter Pence.  
    gid_2008_09_24_cinmlb_houmlb_1  1   4   2   3   3   429665  150116  Edwin Encarnacion called out on strikes.  
    gid_2008_09_24_cinmlb_houmlb_1  1   5   1   2   0   430565  459371  Kazuo Matsui singles on a line drive to right fielder Jay Bruce.  
-----------------------------------------------------------------------------------------

Table: Gameday.pitches
 GameName                   GameAtBatID      Result
------------------------------------------------------
gid_2008_09_24_cinmlb_houmlb_1  1       Called Strike
gid_2008_09_24_cinmlb_houmlb_1  1       Ball
gid_2008_09_24_cinmlb_houmlb_1  1       Swinging Strike
gid_2008_09_24_cinmlb_houmlb_1  1       Ball
gid_2008_09_24_cinmlb_houmlb_1  1       Foul
gid_2008_09_24_cinmlb_houmlb_1  1       Foul
gid_2008_09_24_cinmlb_houmlb_1  1       Swinging Strike
gid_2008_09_24_cinmlb_houmlb_1  2       Ball
gid_2008_09_24_cinmlb_houmlb_1  2       In play, out(s)
gid_2008_09_24_cinmlb_houmlb_1  3       Called Strike
gid_2008_09_24_cinmlb_houmlb_1  3       Ball
--------------------------------------------------------

Table:batters
   GameName                     id         name_display_first_last
----------------------------------------------------------------------------------
gid_2008_09_24_cinmlb_houmlb_1  407783      Geoff Geary
gid_2008_09_24_cinmlb_houmlb_1  209315      David Newhan
gid_2008_09_24_cinmlb_houmlb_1  115629      LaTroy Hawkins
gid_2008_09_24_cinmlb_houmlb_1  113889      Darin Erstad
gid_2008_09_24_cinmlb_houmlb_1  457803      Jay Bruce
gid_2008_09_24_cinmlb_houmlb_1  433898      Jeff Keppinger
gid_2008_09_24_cinmlb_houmlb_1  458015      Joey Votto
gid_2008_09_24_cinmlb_houmlb_1  429665      Edwin Encarnacion
---------------------------------------------------------------------------

我使用的代码如下:

    SELECT gameday.atbats.event
         , gameday.atbats.inning
         , gameday.batters.name_display_first_last
         , gameday.pitchers.name_display_first_last
         , gameday.pitches.result
      FROM gameday.atbats
inner join gameday.pitches on gameday.atbats.num = gameday.pitches.gameAtBatID
                          and gameday.pitches.gamename=gameday.atbats.gamename
inner join gameday.batters on gameday.atbats.batter = gameday.batters.ID
                          AND gameday.atbats.gamename = gameday.batters.gameName
     where gameday.atbats.gamename = "gid_2008_09_24_cinmlb_houmlb_1"

当前代码会导致每个球员的成绩加倍，例如，如果比赛中的第一个击球手在 3 个球上三振，那么当前代码将输出该击球手在第一局 6 个球上三振两次。我正在加入游戏 ID 和每个 table 的 ID，但似乎仍然存在某种问题

Answer 1

无意冒犯，但我不喜欢“使用不同的”答案，因为它只会掩盖问题。在这种情况下，我所做的就是在 FROM 正上方插入 SELECT COUNT(*) 行，然后逐步查看一次添加一行的结果；即，SELECT COUNT(*) FROM A，然后A JOIN B，然后A JOIN B JOIN C，等等来检测有问题的加入。