php - 子查询格式问题

Question

我无法为子查询获取正确的格式。

我的一个查询显示如下:

这涉及调用三个表(Ind Stats 表，提取游戏统计数据和团队名称)。如您所见，此查询运行良好。

我遇到的麻烦是如何拉动每个在该特定游戏中拥有 DD 的玩家。 Players 是数据库中的另一个功能。我所能理解的是我应该需要一个子查询，但子查询不是我的强项。

这是我的代码

  SELECT * FROM 

        (SELECT game_Date, 
        game_GameID,
        game_SeasonID,
        game_TeamCode,
        teams_Name,
        stats1_GameID,
        Sum(stats1_DD) AS 'TotGameDD'

        FROM game INNER JOIN teams INNER JOIN stats1
        ON 
        teams_TeamCode = game_TeamCode
        AND
        game_GameID = stats1_GameID

        GROUP BY game_GameID
        HAVING Sum(stats1_DD) >1
        ORDER BY Sum(stats1_DD) desc) t

    INNER JOIN  players INNER JOIN stats1 INNER JOIN game 
    ON stats1_GameID = game_GameID
            AND
    sstorm_rawstats1.stats1_PlayerID = sstorm_players.player_PlayerID

”

所以，要清楚，这工作正常:

  SELECT game_Date, 
    game_GameID,
    game_SeasonID,
    game_TeamCode,
    teams_Name,
    stats1_GameID,
    Sum(stats1_DD) AS 'TotGameDD'

    FROM game INNER JOIN teams INNER JOIN stats1
    ON 
    teams_TeamCode = game_TeamCode
    AND
    game_GameID = stats1_GameID

    GROUP BY game_GameID
    HAVING Sum(stats1_DD) >1
    ORDER BY Sum(stats1_DD) desc

我只是想在此处的行中显示记录 DD 的玩家的姓名。我现在知道名字了，但它正在计算玩家玩过的所有游戏。我现在快到了。

更新:我现在可以在表格中显示结果，但它只显示一名获得 DD 的玩家。我在想我需要一个循环来显示所有拥有 DD 的用户。任何帮助，非常感谢。

我只是通过在 FROM 语句中添加一个 GROUP BY stats1_DD 和另一个 AND 来更改上面的代码:

  FROM game INNER JOIN teams INNER JOIN stats1
    ON 
    teams_TeamCode = game_TeamCode
    AND
    game_GameID = stats1_GameID
    AND
    game_PlayerID = player_PlayerID


    GROUP BY game_GameID, stats1_DD
    HAVING Sum(stats1_DD) >1
    ORDER BY Sum(stats1_DD) desc

Answer 1

您应该将第三个表与来自您已有查询的结果集连接，或者直接在目标列 DD 上连接第三个表。像这样的东西:

SELECT * 
FROM YourCrrentQuery t
INNER JOIN ThePlayersTable p ON p.DD = t.DD;

或:

SELECT * 
FROM YourCrrentQuery t
WHERE DD IN (SELECT DD 
             FROM ThePlayersTable 
             WHERE DD IS NOT NULL);

---

更新:您需要JOIN子查询与Players 表，如下所示:

SELECT * 
FROM 
(
   SELECT 
     game_Date, 
     game_GameID,
     game_SeasonID,
     game_TeamCode,
     teams_Name,
     stats1_GameID,
     Sum(stats1_DD) AS 'TotGameDD'
     FROM game 
     INNER JOIN teams 
     INNER JOIN stats1  ON teams_TeamCode = game_TeamCode
                       AND game_GameID    = stats1_GameID
     GROUP BY game_GameID
     HAVING Sum(stats1_DD) >1
) t
INNER JOIN players p ON t.TotGameDD = p.DD
ORDER BY t.TotGameDD desc;