php - 如何在MySQL中订购体育联赛表？

我已经有了联赛积分榜，但是，我想让它考虑到2平等于一场胜利，在平局中，最低点对优先。
目前显示如下，但从技术上讲，Team2应该赢了：

Team    W   L   T   PF  PA  PCT
—————————————————————————————————
Team1   7   3   0   247 139 0.7000
Team2   6   2   2   220 122 0.6000
Team3   6   4   0   191 191 0.6000
Team4   4   5   1   167 201 0.4000
Team5   3   6   1   142 202 0.3000
Team6   2   8   0   193 305 0.2000

这是SQL：

SELECT team
 , COUNT(*)  played
 , SUM(win)  wins
 , SUM(loss) lost
 , SUM(win)/count(*) pctWon
 , SUM(draw) draws
 , SUM(SelfScore) ptsfor
 , SUM(OpponentScore) ptsagainst
 , SUM(SelfScore) - SUM(OpponentScore) goal_diff
 , SUM(3*win + draw) score
FROM (
  SELECT team
 , SelfScore
 , OpponentScore
 , SelfScore > OpponentScore win
 , SelfScore < OpponentScore loss
 , SelfScore = OpponentScore draw
  FROM (
    SELECT HomeTeam team, HomeScore SelfScore, AwayScore OpponentScore
    FROM Game
    union all select AwayTeam, AwayScore, HomeScore
    FROM Game
   ) a
) b
GROUP BY team
ORDER BY wins DESC, draws DESC, lost ASC, goal_diff DESC;

最佳答案

看看您的ORDER BY子句：您实际上要求按wins降序排列结果。这条规则优先于所有其他规则，所以很明显1队获胜。

ORDER BY wins DESC, draws DESC, lost ASC, goal_diff DESC;

我想考虑到2平等于1胜，
在平局中，最低点的对手优先。
那就是：

ORDER BY (wins*2 + draws) DESC, lost ASC, ptsagainst DESC;