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

Question

I already have a league standings table, however, I want to make it take into account that 2 draws are equal to a win, and in a tie, lowest points against takes precedence.

Currently shows like below, but technically Team2 should have won:

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

This is the 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;

Answer 1

Take a look at your ORDER BY clause: you are effectively asking for the results to be ordered by order of wins descending. This rule takes preference over all the others, so obviously Team1 wins.

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

I want to make it take into account that 2 draws are equal to a win, and in a tie, lowest points against takes precedence.

Then that would be:

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