我有一个名为‘家’的表，存储的是阿森纳足球俱乐部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分的球队的数量
如果我没理解错的话，下面是你能做到的：
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<>在此拨弄
