在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
);