mysql - SQL从游戏数据库中获取团队记录

Question

对于我的一个体育相关项目，我需要显示球队的记录数。

示例:“第 1 队”和“第 2 队”进行了 5 场比赛。 “1 队”赢了 3 次，“2 队”赢了 2 次。所以输出应该是(3-2)。

这是包含一些数据的表结构。

ID ---- TEAM1 ---- SCORE1 ---- TEAM2 ---- SCORE2
1       70         1           73         2
2       74         0           70         1
3       74         2           73         1
4       73         1           70         0

输出应该是这样的:

TEAM1 ---- TEAM2---- RECORD
70         73         2-0
74         70         0-1
74         73         1-0

注意:

• 总会有赢家，没有一场比赛可以平局。
• 在输出中，team1 和 team2 的组合应该是唯一的。

SQL Fiddle : http://sqlfiddle.com/#!2/4dead/1/0

Answer 1

这里的棘手问题是要处理同一支球队在主场和客场的比赛。这可以通过(很多)case 语句来解决。

基本方法是重新格式化数据，使 ID 最小的团队最先出现。

select 
   least(homeTeam, awayTeam) team1,
   greatest(homeTeam, awayTeam) team2,
   sum(case when awayTeam > homeTeam
       then case when homeTeamScore > awayTeamScore then 1 else 0 end
       else case when homeTeamScore > awayTeamScore then 0 else 1 end
   end) team1Wins,
   sum(case when hometeam > awayteam
       then case when homeTeamScore > awayTeamScore then 1 else 0 end
       else case when homeTeamScore > awayTeamScore then 0 else 1 end
   end) team2Wins
from
  games
group by
   least(homeTeam, awayTeam),
   greatest(homeTeam, awayTeam);

SQL Fiddle

或稍微更紧凑，但可能更难理解:

select 
   least(homeTeam, awayTeam) team1,
   greatest(homeTeam, awayTeam) team2,
   sum(case sign(awayTeam - homeTeam) 
       when sign(homeTeamScore - awayTeamScore) then 1
       else 0 end) team1Wins,
   sum(case sign(awayTeam - homeTeam) 
       when sign(awayTeamScore - homeTeamScore) then 1
       else 0 end) team2Wins
from
  games
group by
   least(homeTeam, awayTeam),
   greatest(homeTeam, awayTeam);

SQL Fiddle