sql - MySQL连接三张表并求平均值

Question

我一直在思考如何构造一个特殊的查询，我能想到的最好的办法是它必须是某种复杂的子查询系统。

虽然我不完全确定。

我有三个表，teams、matches 和 scored。

teams 表包含团队编号和相应团队名称的列表。

matches 表包含记录的比赛结果列表(每场比赛每队一行)，以及相应的队号。

scored 表包含有关球队得分的所有信息的列表，以及相应的比赛结果 ID。

┌────────────┐
| TEAMS      |
├────────────┤
│ teamnumber |
│ teamname   |
└────────────┘

┌───────────────┐
| MATCHES       |
├───────────────┤
│ teamnumber    |
│ matchresultid |
└───────────────┘

┌───────────────┐
| SCORED        |
├───────────────┤
│ matchscoredid |
│ matchresultid |
└───────────────┘

给定一个 teamnumber，我需要在 scored 中获取每个 matchresultid 的平均行数。我该怎么做？

Answer 1

我认为您遗漏的一个可能让其他人感到困惑的因素是给定球队比赛的实际比分。所以基本上你想要一支球队，并且在整个赛季中，你想要他们得分的平均 # 分...

select 
      t.teamnumber,
      t.teamname,
      avg( s.totalpoints ) TeamAvgPoints
   from 
      teams t
         join matches m
            on t.teamnumber = m.teamnumber
            join scored s
               on m.matchresultid = s.matchresultid
   where 
      t.teamnumber = SomeValue

如果您想要比较所有团队的平均值，请忽略 WHERE 子句并按...进行分组

   group by
      t.teamnumber, 
      t.teamname

如果计分表每场比赛有多行，那么每场比赛需要进行预聚合以首先获得总分，然后获得平均分......类似

select
      PreQuery.TeamNumber,
      PreQuery.TeamName,
      avg( ifnull( PreQuery.MatchTotalPoints, 0 ) ) AvgPerGame
   from 
      ( select 
            t.teamnumber,
            t.teamname,
            m.matchresultid,
            count(*) MatchTotalPoints
         from 
            teams t
               left join matches m
                  on t.teamnumber = m.teamnumber
                  left join scored s
                     on m.matchresultid = s.matchresultid
         where 
            t.teamnumber IN( SomeValue, AnotherValue, MoreValues, EtcValue )
         group by
            t.teamnumber,
            t.teamname,
            m.matchresultid ) PreQuery
   group by 
       PreQuery.TeamNumber,
       PreQuery.TeamName

在查询的这个版本中，如果您想比较所有团队，请删除特定团队的内部 WHERE 子句，并将分组依据应用于按团队编号和团队名称进行的外部查询。

为了获得整个列表的额外限定符，我已更改为 LEFT 联接，并以 IFNULL() 结束平均...