MySQL连接三个表并求平均值

Question

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

不过我并不完全确定。

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

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

matches 表包含记录的比赛结果列表（每场比赛每队一行）以及相应的球队编号。

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

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

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

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

给定一个团队号码，我需要获取每个 matchresultid 的 scored 的平均行数。我该怎么做呢？

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 子句，并将 group by 应用到按 teamnumber 和 teamname 进行的 OUTER 查询。

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

Answer 2

您需要分数数量还是平均分数？如果需要平均分，则需要将“count”替换为“avg”

SELECT s.matchresultid, COUNT(s.matchscoredid) 
  FROM matches m INNER JOIN scored s ON m.matchresultid = s.matchresultid
 WHERE m.teamnumber = <team>
GROUP BY s.matchresultid

Answer 3

试试这个：

SELECT s.matchresultid, count(*) 
FROM scored s
JOIN matches m
ON m.matchresultid = s.matchresultid
WHERE m.teamnumber = your_team_number
GROUP BY s.matchresultid

Answer 4

SELECT a.teamname team, b.matchresultid match, COUNT(c.matchscoredid) scores 
FROM teams a, matches b, scored c 
WHERE a.teamnumber = b.teamnumber AND b.matchresultid = c.matchresultid;

这应该返回每支球队每场比赛的得分数。