在MySQL中插入数据后，对两个数值进行条件性增加

Question

我目前在一个足球数据库中有以下三个表：

teams(name) 
season(name, beginning, end)
game(id, Date, season, hometeam, awayteam, HomeTeamScore, AwayTeamScore)
      (hometeam, awayteam和season是外键)

现在我想要有一个新的表，用来记录每个球队进球和失球的情况，以及他们在每个赛季的积分（每场平局一分，每场胜利三分）。这将使得排名很容易获得。

我考虑创建一个像这样的表：

stats(season, team, goalsscored, goalsconcedded, points)

然后每次插入新的比赛时，我也会更新这个表。这个表将包含每个球队+赛季组合的一行。我不确定这是否是最好的解决方案，因为我知道我引入了冗余，但由于这些信息需要经常计算，我认为它可能会有用。我想创建一个触发器来更新这些信息，但我真的不知道如何做到这一点：根据参与比赛的球队，我需要更新stats表中的两行，并且根据他们是主场还是客场，我需要用不同的值来更新它们。

理想情况下，如果球队还没有为比赛所属的赛季插入记录，这个触发器应该在这个新表中创建一个条目，但我甚至不确定在MySQL中是否可能存在这样的条件。 我知道我没有提供任何我所做的测试，但这是因为我真的找不到类似的在线请求（或更一般地说，能够轻松查询所需信息的请求）。

此外，我对如何处理这种情况的更好想法持开放态度。

Answer 1

我认为你应该考虑这样的结构：

Teams(id,name)
Seasons(id, year,begin_date,end_date)
Games(id, date, season (外键), hometeam (外键), awayteam (外键), HomeTeamScore, AwayTeamScore)

这个结构也不是最佳的。 在我个人的意见中，你可以采用更好的结构：

Teams(id,name)
Seasons(id, year,begin_date,end_date)
Matches(id, date, season (外键), home_team (外键), away_team (外键))
Goals(id,team,game,player?)

Goal表将用于记录每个进球，然后你可以从中构建比赛结果，避免使用“HomeTeamScore”和“AwayTeamScore”字段。

至于Stats表，你需要知道谁赢得了积分，所以让我们坚持使用我们最后的表结构：

Teams(id,name)
Seasons(id, year,begin_date,end_date)
Matches(id, date, season (外键), home_team (外键), away_team (外键), status)
Goals(id,team,game,player?)

Matches中的status字段值可以是：['1','X','2']

• 1 - 主队获胜
• X - 平局
• 2 - 客队获胜

这样，你就可以轻松地拥有一切来计算你的统计数据，例如对于ID为12的球队：

Select * from Matches where home_team = 12 and result = '1';
Select * from Matches where away_team = 12 and result = '2';
Select * from Matches where home_team = 12 or away_team=12 and result='X';

你可以以此为起点，构建一个稍微复杂一点的查询，使用group by和group函数来计算球队的统计数据。 一旦你成功创建了这样的查询，我建议使用视图

顺便说一下，你想要执行的这些查询并不重，你不一定需要触发器，首先考虑好数据库设计！

Answer 2

比使用触发器维护冗余数据要简单得多的方法是使用视图；这只是一个基本的联合求和：

create view stats as (
    select season, team, sum(goalsscored) goalsscored, sum(goalsconcedded) goalsconcedded, sum(points) points
    from (
        select season, hometeam team, HomeTeamScore goalsscored, AwayTeamScore goalsconcedded,
            case when HomeTeamScore > AwayTeamScore then 3 when HomeTeamScore=AwayTeamScore then 1 else 0 end points
        from game
        union all
        select season, awayteam team, AwayTeamScore goalsscored, HomeTeamScore goalsconcedded,
            case when AwayTeamScore > HomeTeamScore then 3 when AwayTeamScore=HomeTeamScore then 1 else 0 end points
        from game
    ) games
    group by season, team
);