mysql - 按两个不同列中的值对列求和

Question

我有一个表，其中包含团队名称(HomeTeam、AwayTeam)的列，这些列是字符串，然后是一些统计数据，然后是列 home_points 和 away_points。我想创建一个查询或过程来总结每个团队的分数并按最高分排序。问题是球队有时是 HomeTeam，有时是 AwayTeam，对应于 home_points 或 away_points。我想我对 SQL 的了解不是那么广泛，我没能找到这个问题的另一个例子。我试图通过一些连接来做到这一点，但我仍然不明白如何解决这个问题。该表如下所示:

id   HomeTeam   AwayTeam   home_points   away_points
1    Team1      Team2      3             0
2    Team3      Team1      1             1
3    Team2      Team4      0             3
4    Team1      Team4      0             3

我希望结果表看起来像:

Position   Team    Points
1          Team4     6
2          Team1     4
3          Team3     1
4          Team2     0

我做了一个 SQL fiddle最小的例子。

提前谢谢你。

Answer 1

使用联合:

SELECT
    ROW_NUMBER() OVER (ORDER BY SUM(Points) DESC) Position,
    Team,
    SUM(Points) AS Points
FROM
(
    SELECT HomeTeam AS Team, home_points AS Points FROM matches UNION ALL
    SELECT AwayTeam, away_points FROM matches
) t
GROUP BY
    Team
ORDER BY
    Points DESC;

此答案假定您使用的是 MySQL 8+( future 任何阅读此问题的读者都可能越来越多地使用它)。如果您使用的是较早版本的 MySQL，我们可以通过用户变量模拟行号来生成您想要的位置。以下是您可以如何做到这一点:

SELECT
    @rn := @rn + 1 AS Position,
    t.Team,
    t.Points
FROM
(
    SELECT
        t.Team,
        SUM(t.Points) AS Points
    FROM
    (
        SELECT HomeTeam AS Team, home_points AS Points FROM matches UNION ALL
        SELECT AwayTeam, away_points FROM matches
    ) t
    GROUP BY
        t.Team
) t, (SELECT @rn := 0) r
ORDER BY
    t.Points DESC;

Demo