如何结合比赛结果来获得阿森纳所有六分的球队数量

Question

我有一个名为‘家’的表，存储的是阿森纳足球俱乐部2003-04赛季在主场的联赛表现，而‘客场’的表存储的是阿森纳在同一赛季客场的表现。

Table `home`:

Opponent           Goal_Scored      Goal_Conceded
Everton                2                 1
Aston Villa            2                 0
Portsmouth             1                 1
Newcastle United       3                 2
Chelsea                2                 1
Tottenham Hotspur      2                 1
Fulham                 0                 0
Blackburn Rovers       1                 0
Wolverhampton Wonderers 3                0
Middlesbrough          4                 1


Table `away`:

Opponent           Goal_Scored      Goal_Conceded
Everton                1                 2
Aston Villa            2                 2
Portsmouth             1                 2
Newcastle United       2                 3
Chelsea                2                 2
Tottenham Hotspur      2                 3
Fulham                 2                 0
Blackburn Rovers       1                 2
Wolverhampton Wonderers 3                2
Middlesbrough          1                 4

注意，一个队赢了得三分，平了得一分，输了得零分。

我想确定阿森纳在哪几支球队身上赢得了所有的六分。

为此，我编写了一个部分代码：

CREATE TABLE home (
    Opponent varchar(60),
    Goal_Scored int(8),
    Goal_Conceded int(8)
);

CREATE TABLE away (
    Opponent varchar(60),
    Goal_Scored int(8),
    Goal_Conceded int(8)
);

INSERT INTO home (Opponent, Goal_Scored, Goal_Conceded)
VALUES 
("Everton",2,1),
("Aston Villa",2,0),
("Portsmouth",1,1),
("Newcastle United",3,2),
("Chelsea",2,1),
("Tottenham Hotspur",2,1),
("Fulham",0,0),
("Blackburn Rovers",1,0),
("Wolverhampton Wonderers",3,0),
("Middlesbrough",4,1);


INSERT INTO away (Opponent, Goal_Scored, Goal_Conceded)
VALUES
("Everton",1,2),
("Aston Villa",2,2),
("Portsmouth",1,2),
("Newcastle United",2,3),
("Chelsea",2,2),
("Tottenham Hotspur",2,3),
("Fulham",2,0),
("Blackburn Rovers",1,2),
("Wolverhampton Wonderers",3,2),
("Middlesbrough",1,4);

为了得到组合结果，我尝试了下面的代码，但它没有产生预期的结果：

select a.opponent
from home a 
where a.goals_scored > a.goals_conceded
union all
select b.opponent
from away b 
where b.goals_scored > b.goals_conceded

但是，我应该如何结合这些结果来获得阿森纳所有6分的球队的数量

Answer 1

如果我没理解错的话，下面是你能做到的：

select t.Opponent, sum(CASE WHEN goal_scored - goal_conceded > 0 THEN 3
WHEN goal_scored - goal_conceded = 0 THEN 1 ELSE 0 end ) points 
from 
( SELECT * FROM home a
  union all
  SELECT * FROM away a
) t
group by Opponent
having points = 6;

DB<>在此拨弄