Here is a simple soccer league table setup that was developed in the MySQL Forum by J Williams and a contributor named "Laptop Alias". The teams table tracks team ID and name, the games table tracks home and away team IDs and goal totals for each game. The query for standings is built by aggregating a UNION of home team and away team game results.

-- ---------------------------------------------------------------------------
-- League table
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=804
-- ---------------------------------------------------------------------------

-- Here is a simple soccer league table setup that was developed in the MySQL
-- Forum by J Williams and a contributor named "Laptop Alias". The teams table
-- tracks team ID and name, the games table tracks home and away team IDs and
-- goal totals for each game. The query for standings is built by aggregating
-- a UNION of home team and away team game results:

DROP TABLE IF EXISTS teams, games;

CREATE TABLE teams(id int primary key auto_increment,tname char(32));
CREATE TABLE games(id int primary key auto_increment, date datetime,
                   hteam int, ateam int, hscore tinyint,ascore tinyint);

INSERT INTO teams VALUES
  (1,'Wanderers'),(2,'Spurs'),(3,'Celtics'),(4,'Saxons');
INSERT INTO games VALUES
  (1,'2008-1-1 20:00:00',1,2,1,0),(2,'2008-1-1 20:00:00',3,4,0,2),
  (3,'2008-1-8 20:00:00',1,3,1,1),(4,'2008-1-8 20:00:00',2,4,2,1);

SELECT * FROM teams;
-- +----+-----------+
-- | id | tname     |
-- +----+-----------+
-- |  1 | Wanderers |
-- |  2 | Spurs     |
-- |  3 | Celtics   |
-- |  4 | Saxons    |
-- +----+-----------+

SELECT * FROM games;
-- +----+---------------------+-------+-------+--------+--------+
-- | id | date                | hteam | ateam | hscore | ascore |
-- +----+---------------------+-------+-------+--------+--------+
-- |  1 | 2008-01-01 20:00:00 |     1 |     2 |      1 |      0 |
-- |  2 | 2008-01-01 20:00:00 |     3 |     4 |      0 |      2 |
-- |  3 | 2008-01-08 20:00:00 |     1 |     3 |      1 |      1 |
-- |  4 | 2008-01-08 20:00:00 |     2 |     4 |      2 |      1 |
-- +----+---------------------+-------+-------+--------+--------+

-- Standings query:
SELECT
  tname 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
    hteam Team,
    1 P,
    IF(hscore > ascore,1,0) W,
    IF(hscore = ascore,1,0) D,
    IF(hscore < ascore,1,0) L,
    hscore F,
    ascore A,
    hscore-ascore GD,
    CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS
  FROM games
  UNION ALL
  SELECT
    ateam,
    1,
    IF(hscore < ascore,1,0),
    IF(hscore = ascore,1,0),
    IF(hscore > ascore,1,0),
    ascore,
    hscore,
    ascore-hscore GD,
    CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END
  FROM games
) as tot
JOIN teams t ON tot.Team=t.id
GROUP BY Team
ORDER BY SUM(Pts) DESC ;
-- +-----------+------+------+------+------+------+------+------+------+
-- | Team      | P    | W    | D    | L    | F    | A    | GD   | Pts  |
-- +-----------+------+------+------+------+------+------+------+------+
-- | Wanderers |    2 |    1 |    1 |    0 |    2 |    1 |    1 |    4 |
-- | Spurs     |    2 |    1 |    0 |    1 |    2 |    2 |    0 |    3 |
-- | Saxons    |    2 |    1 |    0 |    1 |    3 |    2 |    1 |    3 |
-- | Celtics   |    2 |    0 |    1 |    1 |    1 |    3 |   -2 |    1 |
-- +-----------+------+------+------+------+------+------+------+------+