从未交手过的回归球队 - SQL

您可以将 teams 表与自身交叉联接以生成所有可能的匹配组合，然后排除那些已经发生的匹配组合：not exists

如果您同时想要 team1/team2 和 team2/team1 游戏，则：

select t1.id_team id_team1, t2.id_team id_team2
from teams t1
inner join teams t2 on t1.id_team != t2.id_team
where not exists (
    select 1 from matches m where m.id_team1 = t1.id_team and m.id_team2 = t2.id_team2
)

如果你想让每对球队之间进行一场比赛，那么我们需要添加一些条件逻辑：

select t1.id_team id_team1, t2.id_team id_team2
from teams t1
inner join teams t2 on t1.id_team < t2.id_team
where not exists (
    select 1 
    from matches m 
    where t1.id_team = least(m.id_team1, m.id_team2)
      and t2.id_team = greatest(m.id_team1, m.id_team2)
)