请尝试以下方法：
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1a1bbfadd62ed68808605a9e7767870d

select hometeam, sum(HomeTeamGoals) as homefor,sum(AwayTeamGoals) as homeagainst, 0 as awayfor, 0 as awayagainst from tablename
group by hometeam

union all

select AwayTeam, 0,0, sum(HomeTeamGoals) as awayfor, sum(AwayTeamGoals) as awayagainst from tablename
group by AwayTeam

输出：

hometeam    homefor homeagainst awayfor awayagainst
team1       7       5            0      0
team4       0       1            0      0
team2       0       0            3      2
team3       0       0            4      4

你可以试试这个
输入：

create table goals(
homeTeam varchar(10),
awayTeam varchar(10),
homeTeamGoals tinyint,
awayTeamGoals tinyint
);
insert into goals values('team1','team2',3,1),('team3','team4',1,2),('team1','team3',4,4),('team4','team2',0,1);

输出：

select totalTeams.team as hometeam,
  case when a.totalHomeGoals IS NULL THEN 0 ELSE a.totalHomeGoals END as totalHomeGoals,
  case when a.totalHomeAgainstGoals IS NULL THEN 0 ELSE a.totalHomeAgainstGoals END as totalHomeAgainstGoals,
  case when b.totalAwayGoals IS NULL THEN 0 ELSE b.totalAwayGoals END as totalAwayGoals,
  case when b.totalAwayAgainstGoals IS NULL THEN 0 ELSE b.totalAwayAgainstGoals END as totalAwayAgainstGoals
      from (select homeTeam as team from goals union select awayTeam from goals) as totalTeams 
      left join (select homeTeam as team, sum(homeTeamGoals) as totalHomeGoals,sum(awayTeamGoals) as totalHomeAgainstGoals from goals group by hometeam) as a on totalTeams.team=a.team
      left join (select awayTeam as team,sum(awayTeamGoals) as totalAwayGoals,sum(homeTeamGoals) as totalAwayAgainstGoals from goals group by awayTeam) as b on totalTeams.team=b.team 
      order by hometeam ;