多表连接时Goals表引发Gamelog行重复的技术解决方案问询

问题背景 #

连接gamelog（赛事记录表）、teams（球队表）和goals（进球记录表）时，因goals表单场赛事对应多条记录，导致赛事对阵信息重复显示，重复次数等于该场进球数。

单独连接gamelog和teams能正常显示单场对阵：

$sql = "SELECT gamelog.game_number, gamelog.home_team_id, gamelog.away_team_id, 
t1.name AS hometeam, t2.name AS awayteam
FROM gamelog
JOIN teams AS t1 ON gamelog.home_team_id=t1.id
JOIN teams AS t2 ON gamelog.away_team_id=t2.id";

$statement = $pdo->query($sql);
$results = $statement->fetchAll();

<?php foreach($results as $result) { ?>
  Game Number: <?= $result['game_number']; ?>
   
    <?= $result['hometeam'].' VERSUS '.$result['awayteam']; ?>
    
    
<?php } ?>

但加入goals表后，赛事信息重复：

$sql = "SELECT gamelog.game_number, gamelog.home_team_id, gamelog.away_team_id, 
t1.name AS hometeam, t2.name AS awayteam,
goals.game_number_id, goals.team_id_goal
FROM gamelog
JOIN teams AS t1 ON gamelog.home_team_id=t1.id
JOIN teams AS t2 ON gamelog.away_team_id=t2.id
JOIN goals ON gamelog.game_number = goals.game_number_id";

$statement = $pdo->query($sql);
$results = $statement->fetchAll();

<?php foreach($results as $result) { ?>
        Game Number: <?= $result['game_number']; ?>
        <br>
        <?= $result['hometeam'].' VERSUS '.$result['awayteam']; ?>
        <br>
        Which team scored the goals: <?= $result['team_id_goal']; ?>
        <br>
        
<?php } ?>

期望输出：每场赛事仅显示一次对阵信息，随后列出该场所有进球记录。尝试过GROUP BY但语法错误，双查询又重复输出进球，寻求解决方法。

附查询返回的$results数组：

array(8) { 
  [0]=> array(11) { ["game_number"]=> int(1) ["home_team_id"]=> int(1) ["away_team_id"]=> int(2) ["home_score"]=> int(2) ["away_score"]=> int(1) ["hometeam"]=> string(3) "MTL" ["awayteam"]=> string(3) "BOS" ["homelogo"]=> string(12) "habs-svg.svg" ["awaylogo"]=> string(14) "bruins-svg.svg" ["game_number_id"]=> int(1) ["team_id_goal"]=> int(1) } 
  [1]=> array(11) { ["game_number"]=> int(1) ["home_team_id"]=> int(1) ["away_team_id"]=> int(2) ["home_score"]=> int(2) ["away_score"]=> int(1) ["hometeam"]=> string(3) "MTL" ["awayteam"]=> string(3) "BOS" ["homelogo"]=> string(12) "habs-svg.svg" ["awaylogo"]=> string(14) "bruins-svg.svg" ["game_number_id"]=> int(1) ["team_id_goal"]=> int(1) } 
  [2]=> array(11) { ["game_number"]=> int(1) ["home_team_id"]=> int(1) ["away_team_id"]=> int(2) ["home_score"]=> int(2) ["away_score"]=> int(1) ["hometeam"]=> string(3) "MTL" ["awayteam"]=> string(3) "BOS" ["homelogo"]=> string(12) "habs-svg.svg" ["awaylogo"]=> string(14) "bruins-svg.svg" ["game_number_id"]=> int(1) ["team_id_goal"]=> int(2) } 
  [3]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(2) } 
  [4]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(2) } 
  [5]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(2) } 
  [6]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(1) } 
  [7]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(1) } 
}

解决方案1：前端循环优化 #

在PHP循环时，先将结果按赛事ID分组，只在赛事ID变化时输出对阵信息，之后统一输出该场所有进球。

代码示例：

// 按赛事ID分组整理数据
$games = [];
foreach ($results as $row) {
    $gameId = $row['game_number'];
    if (!isset($games[$gameId])) {
        // 初始化赛事基础信息
        $games[$gameId] = [
            'game_number' => $row['game_number'],
            'hometeam' => $row['hometeam'],
            'awayteam' => $row['awayteam'],
            'home_score' => $row['home_score'],
            'away_score' => $row['away_score'],
            'goals' => []
        ];
    }
    // 追加进球记录
    $games[$gameId]['goals'][] = $row['team_id_goal'];
}

// 遍历分组后的数据输出
foreach ($games as $game) {
?>
    <div class="game-card">
        Game Number: <?= $game['game_number']; ?><br>
        <?= $game['hometeam'] . ' VS ' . $game['awayteam']; ?><br>
        Final Score: <?= $game['home_score'] . ' - ' . $game['away_score']; ?><br>
        <strong>Goal History:</strong><br>
        <?php foreach ($game['goals'] as $teamId) { ?>
            - Team ID <?= $teamId; ?> scored<br>
        <?php } ?>
    </div>
    <hr>
<?php
}

解决方案2：SQL查询优化（聚合进球信息） #

使用GROUP_CONCAT将单场赛事的所有进球球队ID合并为一个字符串，让每条赛事记录仅出现一次，前端再拆分字符串展示进球。

SQL查询语句：

SELECT 
    gamelog.game_number,
    gamelog.home_team_id,
    gamelog.away_team_id,
    gamelog.home_score,
    gamelog.away_score,
    t1.name AS hometeam,
    t2.name AS awayteam,
    t1.logo AS homelogo,
    t2.logo AS awaylogo,
    GROUP_CONCAT(goals.team_id_goal SEPARATOR ',') AS goal_teams
FROM gamelog
JOIN teams AS t1 ON gamelog.home_team_id = t1.id
JOIN teams AS t2 ON gamelog.away_team_id = t2.id
LEFT JOIN goals ON gamelog.game_number = goals.game_number_id
GROUP BY gamelog.game_number, gamelog.home_team_id, gamelog.away_team_id, gamelog.home_score, gamelog.away_score, t1.name, t2.name, t1.logo, t2.logo

前端输出代码：

$statement = $pdo->query($sql);
$results = $statement->fetchAll();

foreach ($results as $game) {
?>
    <div class="game-card">
        Game Number: <?= $game['game_number']; ?><br>
        <?= $game['hometeam'] . ' VS ' . $game['awayteam']; ?><br>
        Final Score: <?= $game['home_score'] . ' - ' . $game['away_score']; ?><br>
        <strong>Goal History:</strong><br>
        <?php 
        $goalTeams = explode(',', $game['goal_teams'] ?? '');
        foreach ($goalTeams as $teamId) {
            if (!empty($teamId)) {
        ?>
                - Team ID <?= $teamId; ?> scored<br>
        <?php 
            }
        }
        ?>
    </div>
    <hr>
<?php
}

注意事项 #

• 使用GROUP_CONCAT时，需确保MySQL的group_concat_max_len参数足够大，避免截断长字符串；若要保留进球顺序，可添加ORDER BY，例如GROUP_CONCAT(goals.team_id_goal ORDER BY goals.id SEPARATOR ',')。
• 用LEFT JOIN替代JOIN，确保无进球的赛事也能正常显示。

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