P粉0186537512023-09-03 09:55:42
我認為你應該考慮這樣的結構:
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']
這樣,你就可以輕鬆地擁有一切來計算你的統計數據,例如對於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函數來計算球隊的統計資料。 一旦你成功創建了這樣的查詢,我建議使用檢視
順便說一下,你想要執行的這些查詢並不重,你不一定需要觸發器,首先考慮好資料庫設計!
P粉9532317812023-09-03 00:42:53
比使用觸發器維護冗餘資料要簡單得多的方法是使用視圖;這只是一個基本的聯合求和:
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 );