mysql - 从 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