您需要一个相关的子查询：

SELECT m.id, m.round_id, m.gameweek
 FROM `match` m
 LEFT JOIN competition_rounds r ON m.round_id = r.id
 LEFT JOIN competition_seasons s ON r.season_id = s.id
 LEFT JOIN competition c ON c.id = s.competition_id
 WHERE 1 AND
 m.status = 5 AND
 m.round_id IN (488, 489, 490, 491) AND
 m.gameweek = (SELECT MAX(m2.gameweek)
                FROM `match` m2
                WHERE m2.round_id = m.round_id)

我想你可能想要：

SELECT m.id, m.round_id, m.gameweek
 FROM `match` m
 LEFT JOIN competition_rounds r ON m.round_id = r.id
 LEFT JOIN competition_seasons s ON r.season_id = s.id
 LEFT JOIN competition c ON c.id = s.competition_id
 WHERE 1 AND
 m.status = 5 AND
 m.round_id IN (488, 489, 490, 491) AND
 m.gameweek = (SELECT MAX(m2.gameweek)
                FROM `match` m2
                WHERE m2.round_id = m.round_id))

我猜这个问题已经足够了：

SELECT m.id, m.round_id, m.gameweek
FROM `match` m
WHERE 1 AND
      m.status = 5 AND
      m.round_id IN (488, 489, 490, 491) AND
      m.gameweek = (SELECT MAX(m2.gameweek)
                    FROM `match` m2
                    WHERE m2.round_id = m.round_id AND
                          m2.status = m.status
                   );

也就是说，您可能还需要考虑状态。而且，你不需要 JOIN 除非你真的想在结果中重复。