Selepas memasukkan data dalam MySQL, tingkatkan dua nilai secara bersyarat

Question

Saya kini mempunyai tiga jadual berikut dalam pangkalan data bola sepak:

pasukan(nama)
musim (nama, permulaan, akhir)
permainan(id, Tarikh, musim, hometeam, awayteam, HomeTeamScore, AwayTeamScore)
      (pasukan tuan rumah, pasukan lawan dan musim adalah kunci asing)

Sekarang saya mahu mempunyai jadual baharu yang merekodkan gol yang dijaringkan dan gol yang dibolosi oleh setiap pasukan, serta mata yang mereka perolehi dalam setiap musim (satu mata untuk setiap seri, tiga untuk setiap kemenangan) . Ini akan menjadikan kedudukan mudah diperoleh.

Saya mempertimbangkan untuk membuat jadual seperti ini:

stats(musim, pasukan, jaringan gol, gol dibolosi, mata)

Kemudian setiap kali padanan baharu dimasukkan, saya turut mengemas kini jadual ini. Jadual ini akan mengandungi satu baris untuk setiap gabungan pasukan+musim. Saya tidak pasti sama ada ini adalah penyelesaian terbaik kerana saya tahu saya memperkenalkan lebihan, tetapi memandangkan maklumat ini perlu dikira dengan kerap, saya fikir ia mungkin berguna. Saya ingin mencipta pencetus untuk mengemas kini maklumat ini, tetapi saya tidak tahu cara melakukannya: bergantung pada pasukan yang mengambil bahagian dalam perlawanan, saya perlu mengemas kini dua baris dalam jadual statistik dan bergantung pada sama ada mereka di rumah atau di luar, saya perlu Kemas kini dengan nilai yang berbeza.

Sebaik-baiknya, pencetus ini harus mencipta entri dalam jadual baharu ini jika pasukan itu belum memasukkan rekod untuk musim yang dimiliki permainan itu, tetapi saya tidak pasti sama ada keadaan sedemikian mungkin berlaku dalam MySQL. Saya tahu saya tidak memberikan apa-apa ujian yang saya lakukan, tetapi itu kerana saya benar-benar tidak dapat mencari permintaan yang serupa dalam talian (atau lebih umum, satu yang memudahkan untuk menanyakan maklumat yang saya perlukan).

Selain itu, saya terbuka kepada idea yang lebih baik tentang cara menangani situasi ini.

Answer 1

Saya rasa anda harus mempertimbangkan struktur seperti ini:

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

Struktur ini juga tidak optimum. Pada pendapat peribadi saya, anda boleh menggunakan struktur yang lebih baik:

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

Jadual Gol akan digunakan untuk merekod setiap gol dan anda kemudian boleh membina keputusan perlawanan daripadanya, mengelakkan penggunaan medan "HomeTeamScore" dan "AwayTeamScore".

Bagi jadual Statistik, anda perlu tahu siapa yang memenangi mata, jadi mari kekal dengan struktur jadual terakhir kami:

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

Nilai medan status dalam Padanan boleh menjadi: ['1','X','2']

• 1 - Pasukan tuan rumah menang
• X - lukis
• 2 - Pasukan tempat lawan menang

Dengan cara ini anda boleh mempunyai segala-galanya dengan mudah untuk mengira statistik anda, contohnya untuk pasukan dengan 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';

Anda boleh menggunakan ini sebagai titik permulaan untuk membina pertanyaan yang sedikit lebih kompleks menggunakan fungsi kumpulan mengikut dan kumpulan untuk mengira statistik pasukan. Sebaik sahaja anda berjaya membuat pertanyaan seperti ini, saya syorkan anda menggunakan Views

By the way, pertanyaan yang ingin anda laksanakan ini tidak berat, anda tidak semestinya memerlukan pencetus, fikirkan tentang reka bentuk pangkalan data dahulu!

Answer 2

Cara yang lebih mudah daripada menggunakan pencetus untuk mengekalkan data berlebihan adalah dengan menggunakan paparan; ia hanyalah jumlah kesatuan asas:

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