问Mysql体育排行榜

EN

SELECT
    name AS Team,
    Sum(P) AS P,
    Sum(W) AS W,
    Sum(D) AS D,
    Sum(L) AS L, 
    SUM(F) as F,
    SUM(A) AS A,
    SUM(GD) AS GD,
    SUM(Pts) AS Pts  
FROM ( 
    SELECT *,
        hometeamid Team,  
        1 P, 
        IF(homegoal > awaygoal,1,0) W, 
        IF(homegoal = awaygoal,1,0) D, 
        IF(homegoal < awaygoal,1,0) L, 
        homegoal F, 
        awaygoal A, 
        homegoal-awaygoal GD, 
        CASE WHEN homegoal > awaygoal THEN 3
             WHEN hometeam = awaygoal THEN 1
             ELSE 0
        END PTS 
    FROM results
    WHERE id_league = '$leagueid'
      AND homegoal IS NOT NULL
      AND awaygoal IS NOT NULL

    UNION ALL 

    SELECT *,
        awayteamid, 
        1, 
        IF(homegoal < awaygoal,1,0), 
        IF(homegoal = awaygoal,1,0), 
        IF(homegoal > awaygoal,1,0), 
        awaygoal, 
        homegoal, 
        awaygoal-homegoal GD, 
        CASE WHEN homegoal < awaygoal THEN 3
             WHEN homegoal = awaygoal THEN 1
             ELSE 0
        END 
        FROM results
        WHERE id_league = '$leagueid' 
          AND awaygoal IS NOT NULL
          AND homegoal IS NOT NULL
) AS tot 
JOIN clubs t ON tot.Team=t.id_clubs  
GROUP BY Team  
ORDER BY
    SUM(Pts) desc,
    SUM(GD) desc