mysql - 如何在 MySQL 中使用聚合和 UNION 解决此问题？

Question

如果我的数据是这样

match homeTeam  awayTeam  homeTeamID  awayTeamID  homePoints awayPoints
  1    Alpha      Beta        1           2           4          2
  2    Gamma      Delta       3           4           6          0
  3    Alpha      Gamma       1           3           2          4
  4    Delta      Beta        4           2           3          3

我需要为他们做一个梯子，但我做不到恰到好处

结果应该是这样的

Name  played  Points
Gamma    2      10
Alpha    2       6
Beta     2       5
Delta    2       3

到目前为止我的代码是这样的

$query = "SELECT *
    FROM (
        SELECT homeTeam AS teamName,
            COUNT(homeTeamID) AS matches_played,
            SUM(if(homeTeamID, homePoints, 0)) AS total_points
            FROM matches
        UNION ALL SELECT awayTeam AS teamName,
            COUNT(awayTeamID) AS matches_played,
            SUM(if(awayTeamID, awayPoints, 0)) AS total_points
            FROM matches
        ) all_points
    GROUP BY teamName
    ORDER BY total_points DESC  ";

但它所做的只是显示 ALPHA 打了 4 场比赛得到 15 分，而 BETA 打了 4 场比赛得到 9 分 - Gamma 和 Delta 消失了:(

Answer 1

您在内部查询中没有 GROUP BY 子句。但它仍然是不正确的。尝试:

SELECT teamName, COUNT(*) AS played, SUM(points) as points
    FROM (
        SELECT homeTeam AS teamName,
            homePoints AS points
            FROM matches
        UNION ALL SELECT awayTeam AS teamName,
            awayPoints AS points
            FROM matches
        ) all_points
    GROUP BY teamName
    ORDER BY total_points DESC