Ho una tabella matches, avente campi:
  • id
  • home_team_id
  • away_team_id
  • home_goal_number
  • away_goal_number


Ho realizzato una query per creare una classifica:
codice:
SELECT squadra, COUNT( squadra ) AS partite, SUM( IF( punteggio =3, 1, 0 ) ) AS vittorie, SUM( IF( punteggio =1, 1, 0 ) ) AS pareggi, SUM( IF( punteggio =0, 1, 0 ) ) AS sconfitte, SUM( punteggio ) AS punteggio, SUM( fatti ) AS fatti, SUM( subiti ) AS subiti, SUM( fatti ) - SUM( subiti ) AS diff_reti
FROM (

SELECT home_team_id AS squadra, home_goal_number AS fatti, away_goal_number AS subiti, 
CASE 
WHEN home_goal_number > away_goal_number
THEN 3 
WHEN home_goal_number = away_goal_number
THEN 1 
ELSE 0 
END AS punteggio
FROM matches
UNION ALL 
SELECT away_team_id AS squadra, away_goal_number AS fatti, home_goal_number AS subiti, 
CASE 
WHEN away_goal_number > home_goal_number
THEN 3 
WHEN away_goal_number = home_goal_number
THEN 1 
ELSE 0 
END AS punteggio
FROM matches
) AS tab
GROUP BY squadra
ORDER BY punteggio DESC
Il tutto funziona bene. Ora il problema è: due squadre a pari punti vengono differenziate in base allo scontro diretto, come posso modificare questa query affinché possa differenziare le squadre in base agli scontri diretti?