mysql - 加入一个表来显示平均值？

Question

我有一个不同球队和不同年份的比赛得分列表。

数据库看起来像这样:

id |team|year|week|points
1 | Wildcats|2015|1|43
2 | Wildcats|2015|2|50

我想创建一个显示，显示数据库中的每支球队、当年的总得分以及当年的联赛平均得分。

所以它可能看起来像:

Wildcats 2015  387  44.3

等等

我正在尝试这个，但它不起作用:

SELECT g1.year, g1.team, xyz.total 
FROM game g1
join (SELECT avg(points) as total, year
            FROM game g2) xyz on g1.year=xyz.year
group by g1.year, g1.team

Answer 1

您可以使用相关子查询来获取每年的平均分:

SELECT team, year, SUM(points) AS totalPoints,  
       (SELECT AVG(points)
        FROM game AS g2
        WHERE g2.year = g1.year) AS avgPoints
FROM game AS g1
GROUP BY team, year

Demo here

或者，连接派生表:

SELECT g1.team, g1.year, SUM(g1.points) AS totalPoints,  
       g2.avgPoints
FROM game AS g1
JOIN (SELECT AVG(points) AS avgPoints, year
      FROM game
      GROUP BY year
) AS g2 ON g1.year = g2.year
GROUP BY team, year

Demo here