我想这个查询会给你你想要的结果。它发现了 team_id 值来自 match_main ，那么 JOIN s到 match_main 再来两次，以获得每个团队的球员名单，以及一个行号，用于确保我们不会重复成对的球员：

SELECT u1.player_name, u2.player_name
FROM (SELECT MIN(team_id) AS team_1, MAX(team_id) AS team_2 FROM match_main) teams
JOIN (SELECT *, @rn1 := @rn1 + 1 AS rownumber
      FROM match_main
      CROSS JOIN (SELECT @rn1 := 0) r
      ORDER BY team_id ASC) m1 ON m1.team_id = teams.team_1
JOIN (SELECT *, @rn2 := @rn2 + 1 AS rownumber
      FROM match_main
      CROSS JOIN (SELECT @rn2 := 0) r
      ORDER BY team_id DESC) m2 ON m2.team_id = teams.team_2 AND m2.rownumber = m1.rownumber
JOIN users u1 ON u1.player_id = m1.player_id
JOIN users u2 ON u2.player_id = m2.player_id

我在dbfiddle上创建了一个小演示，这样您就可以在工作中看到它并使用它。
第一个派生表：

SELECT MIN(team_id) AS team_1, MAX(team_id) AS team_2 FROM match_main

在每场比赛中找到两个独立的队伍。第二个和第三个派生表：

SELECT *, @rn1 := @rn1 + 1 AS rownumber
FROM match_main
CROSS JOIN (SELECT @rn1 := 0) r
ORDER BY team_id ASC

本质上是一样的。它们为表中的每一行生成行号 match_main 表，按 team_id 上升或下降，使每个队的队员都进入上半部分。这个 m2.rownumber = m1.rownumber 确保我们在最终结果中得到每个团队的球员。最后两个 JOIN s到 users 使我们能够从该表中获取玩家名称。
更新
使用团队id match_master （为了确保主客场球员被正确分类）你需要使用 {match_id} 查询中的值（共3处）；这样的方法应该有用：

SELECT u1.player_name, u2.player_name
FROM match_master teams
JOIN (SELECT *, @rn1 := @rn1 + 1 AS rownumber
      FROM {match_id}_match_main
      CROSS JOIN (SELECT @rn1 := 0) r
      ORDER BY team_id ASC) m1 ON m1.team_id = teams.team1_id
JOIN (SELECT *, @rn2 := @rn2 + 1 AS rownumber
      FROM {match_id}_match_main
      CROSS JOIN (SELECT @rn2 := 0) r
      ORDER BY team_id DESC) m2 ON m2.team_id = teams.team2_id AND m2.rownumber = m1.rownumber
JOIN users u1 ON u1.player_id = m1.player_id
JOIN users u2 ON u2.player_id = m2.player_id
WHERE teams.match_id = {match_id}