由内连接产生的重复数据

Question

我一直在绞尽脑汁想弄清楚到底出了什么问题。下面是我正在使用的表格：

 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和每个表中的ID，但似乎仍然存在一些问题

Answer 1

无意冒犯，但我不喜欢“使用distinct”的答案，因为这只会掩盖问题。在这种情况下，我所做的就是在FROM的正上方插入一个SELECT COUNT(*)行，然后逐步查看结果，一次添加一行；例如，SELECT COUNT(*) FROM A，然后是A JOIN B，然后是A JOIN B JOIN C，依此类推，以检测违规的JOIN。

Answer 2

您需要在Where条件之后添加以下内容，例如

AND gameday.atbats.Id = Gameday.pitches.GameAtBatID      
AND batters.ID = gameday.atbats.batters

Answer 3

在最后一行的查询中，您已经使用游戏名称编写了where条件，如果游戏名称不是唯一的，那么您将获得多个答案，因此在gameday.atbats表的where条件中使用主键是更好的方法。