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

For one of my sports related project, I need to show the record counts of teams.

对于我的一个体育项目，我需要展示团队的记录计数。

Example: "Team 1" and "Team 2" has played 5 games. "Team 1" has won 3 times and "Team 2" has won 2 times. So the output should be (3-2).

例如:“第一组”和“第二组”已经打了5场比赛。“1队”赢了3次，“2队”赢了2次。所以输出应该是(3-2)

Here is the table structure with some data.

这是具有一些数据的表结构。

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

The output should be something like:

输出应该如下所示:

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

NOTE:

注意:

• There will be always a winner and no game can be a draw.
• 总有赢家，没有平局。
• In the output, the combination of team1 and team2 should be unique.
• 在输出中，team1和team2的组合应该是唯一的。

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

SQL小提琴:http://sqlfiddle.com/ ! 2/4dead / 1/0

1 个解决方案

#1

The tricky thing here is dealing with the same teams playing in home and away fixtures. This can be worked around with (lots of) case statements.

这里的棘手之处在于如何处理主场和客场的比赛。这可以通过(大量)案例陈述来解决。

The basic approach is to reformat the data so that the team with the lowest id appears first.

基本的方法是重新格式化数据，以使具有最低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

SQL小提琴

or slightly more compact, but possibly harder to understand:

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

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

SQL小提琴