sql - 如何根据此 SQL 表计算总输赢

Question

我有一个名为 games(game_id, home_id, home_score, away_id, away_score, date) 的表和一个名为 team(team_id, team_name) 的表。我需要一个 SQL 查询来根据 home_score 和 away_score 计算每支球队的总输赢和赢百分比（赢/场数）记录。

select game_id, home_score, away_score, case when home_score > away_score then 'true' else 'false ' end from game_schedule

试过这个，但不能让它做我想做的事。谢谢！

标签： sql

Answer 1

反透视数据并聚合：

select team_id, sum(is_win) as num_wins, sum(is_loss) as num_losses,
       avg(is_win) as win_ratio
from ((select home_id as team_id,
              (case when home_score > away_score then 1 else 0 end) as is_win,
              (case when home_score < away_score then 1 else 0 end) as is_loss
       from games
      ) union all
      (select away_id,
              (case when away_score > home_score then 1 else 0 end) as is_win,
              (case when away_score < home_score then 1 else 0 end) as is_loss
       from games
      )
     ) g
group by team_id;