用于获取2队比赛记录的SQL语句

Question

我有一张表和一个游戏列表，它有团队ID和游戏中的分数。

这是表结构。

ID HOMETEAM HOMETEAMSCORE AWAYTEAM AWAYTEAMSCORE 

• 各队在一场比赛中得分将在各列。
• 当比赛没有开始时，两队的比分都是0。
• 当比赛结束时，没有比赛可以有0-0比分。

我需要获得2个特定团队的游戏记录。例如，对于球队1和球队2，我需要输出为1-3，这意味着球队1赢得了一次对阵球队2，球队2赢得了3次球队1的冲击。

我正在使用下面的SQL它在一定程度上起作用。但是我需要为0-0还没有开始的比赛输出（比分0-0）。

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 ow_sports_games 
GROUP BY least(homeTeam, awayTeam), 
     greatest(homeTeam, awayTeam) 

还有没有更好的方法来有更好的SQL以外的呢？

SQL小提琴：http://sqlfiddle.com/#!2/10326/4/1

编辑：

一些样本数据来解释我的要求的详细信息：

HOMETEAM HOMETEAMSCORE AWAYTEAM AWAYTEAMSCORE 
18   1    22    0 
22   2    18    1 
18   3    22    2 
12   0    13    0 

对于球队18,22输出应该是2-1，因为18队有2胜，22队有1胜。

对于球队12和13，输出应该是0-0，因为没有比赛完成。

输出样本解释总体情况：

HomeTeam  AwayTeam  Records 
18    22   2-1 
22    18   1-2 
12    13   0-0 

Answer 1

这是你输出相匹配的问题陈述（第3列是相同的，后两种排序，并确保我考虑到，当他们的避风港“T发挥，但有一个记录。见http://sqlfiddle.com/#!2/10326/43

SELECT DISTINCT hometeam, awayteam 
    , CONCAT(IF(hometeam = LEAST(hometeam, awayteam),team1wins, team2wins),' - ', IF(awayteam = LEAST(hometeam, awayteam),team1wins, team2wins)) AS Head2HeadRecord 
    , CONCAT(LEAST(hometeam, awayteam), GREATEST(hometeam, awayteam)) AS surrogateSort 
    , notplayed 
FROM ow_sports_games 
JOIN (SELECT LEAST(hometeam, awayteam) AS team1 
     ,GREATEST(hometeam, awayteam) AS team2 
     ,SUM(IF(LEAST(hometeam, awayteam) = hometeam AND homeTeamScore > awayTeamScore, 1, 
       IF(LEAST(hometeam, awayteam) = awayteam AND awayTeamScore > homeTeamScore,1,0) 
      ) 
      ) AS team1Wins 
     ,SUM(IF(GREATEST(hometeam, awayteam) = hometeam AND homeTeamScore > awayTeamScore, 1, 
       IF(GREATEST(hometeam, awayteam) = awayteam AND awayTeamScore > homeTeamScore,1,0) 
      ) 
      ) AS team2Wins 
     ,SUM(IF(homeTeamScore=0 AND awayTeamScore=0,1,0)) AS notPlayed 
     FROM ow_sports_games 
     GROUP BY team1, team2) AS trecords 
    ON LEAST(hometeam, awayteam) = team1 
    AND GREATEST(hometeam, awayteam) = team2 
ORDER BY surrogateSort, hometeam,awayteam;