mysql - SQL足球比赛，如何从2组中选择淘汰赛阶段

Question

我正在尝试选择：
A 组第一队对阵 B 组第二名 B 组
第一队对阵 A 组第二名

我看到这个作业有很多问题，但不是针对 2 组

create table games
(
    Home_team varchar(20),
    Away_team varchar(20),
    goals_for integer,
    goals_againts integer,
    group_stage varchar(20)
);

insert into games values ('Argentina','Nigeria',2,0,'A');
insert into games values ('Germany','Japan',1,1,'A');
insert into games values ('Japan','Argentina',0,1,'A');
insert into games values ('Germany','Nigeria',2,3,'A');
insert into games values ('Nigeria','Japan',0,0,'A');
insert into games values ('Germany','Argentina',1,0,'A');
insert into games values ('Brazil','Turkey',2,1,'B');
insert into games values ('China','Costa-Rica',0,2,'B');
insert into games values ('Brazil','China',4,0,'B');
insert into games values ('Costa-Rica','Turkey',1,1,'B');
insert into games values ('Costa-Rica','Brazil',2,5,'B');
insert into games values ('Turkey','China',3,0,'B');

select
    Team,
    sum(goalsfor) Scored, 
    sum(goalsagaints) Recived, 
    sum(goalsfor) - sum(goalsagaints) GoalDiff,
    sum(
          case when goalsfor > goalsagaints then 3 else 0 end 
        + case when goalsfor = goalsagaints then 1 else 0 end
    ) Points
     From (Select Home_team Team, home_goals goalsfor , away_goals goalsagaints from games
             union all
             Select Away_team, away_goals, home_goals from games ) My_standing
group by Team
order by Points desc, GoalDiff desc

现在我有了地位，我希望结果看起来像这样：

Argentina  VS     Turkey


Brazil    VS     Germany   

标签： mysqlsql