从select语句连接两个表时出现问题

Question

到目前为止，我已经写了一份精选声明，获取球队的名称和他们在主场比赛中的平均得分，以及他们输给另一个球队的平均得分。

当他们客场时，我还有另一个，我选择了客队，以及他们客场时的平均得分和他们的平均失球数。

我如何从选择查询中连接这两个表，以便得到一个带有球队名称、主场平均得分、主场平均失球、客场平均失球和客场平均得分的表？

以下是我的代码:

select home_team, avg(home_score_half + home_score_full) as avg_scored_home, 
       avg(away_score_half + away_score_full) as avg_conceded_home 
from matches 
group by home_team;

select away_team, avg(away_score_half + away_score_full) as avg_scored_away, 
       avg(home_score_half + home_score_full) as avg_conceded_away 
from matches 
group by away_team;

任何帮助都将不胜感激。谢谢！

Answer 1

如果您希望结果连接（每个团队在同一行），您可以使用两个查询作为连接的子查询

select  t1.home_team
      , t1.avg_scored_home
      , t1.avg_conceded_home
      , t2.away_team
      , t2.avg_scored_away
      , t2.avg_conceded_away
from (
  select home_team
      , avg(home_score_half + home_score_full) as avg_scored_home
      , avg(away_score_half + away_score_full) as avg_conceded_home 
  from matches 
  group by home_team
) t1 
INNER JOIN (
  select away_team
    , avg(away_score_half + away_score_full) as avg_scored_away
    , avg(home_score_half + home_score_full) as avg_conceded_away 
  from matches group by away_team
) t2 ON t1.home_team = t2.away_team