php - 梦幻足球: Compare a team's average points to the league average?

Question

我在 MySQL 表中有梦幻足球联赛的数据。我想查询一个 php 页面的数据。

我如何创建一个查询来计算球队得分与该特定年份联盟平均得分的比较？

我的(简化的)数据表如下所示:

游戏 table :

year | team | Week | Points
2016 | Wildcats | 1 | 44
2016 | Wildcats | 2 | 52
2015 | Hawks | 1 | 36
2015 | Hawks | 2 | 58
2016 | Hawks | 1 | 43

我如何找出野猫队 2016 年的平均得分 (48.0) 与 2016 年所有球队的平均得分 (46.3) 之间的差异？

我可以使用此查询计算这两个数字，但不确定如何进行减法。

SELECT g1.year, g1.team, AVG(g1.points) as ppg,
    (SELECT AVG(points)
    FROM game AS g2
    WHERE g2.year=g1.year) AS seasAvg
FROM game AS g1
GROUP BY g1.team, g1.year
ORDER BY ppg

Answer 1

将整个事情包装在一个外部查询中，然后您可以计算出差异，同时保持简短。

SELECT a.*, ppg - seasAvg AS diff FROM (
    SELECT g1.year, g1.team, AVG(g1.points) as ppg,
        (SELECT AVG(points)
        FROM game AS g2
        WHERE g2.year=g1.year) AS seasAvg
    FROM game AS g1
    GROUP BY g1.team, g1.year
    ORDER BY ppg
) AS a