Skor Pertandingan Bola

DROP TABLE IF EXISTS `tbl_tim`;
CREATE TABLE IF NOT EXISTS `tbl_tim` (
  `id_tim` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `nama_tim`  varchar(30) NOT NULL,
  PRIMARY KEY (`id_tim`)
) ENGINE=MyISAM;

INSERT INTO `tbl_tim`(`nama_tim`)
VALUES
('PS Pertamax'),
('Duo United'),
('Three City'),
('Quad FC');

SELECT * FROM tbl_tim;
+--------+-------------+
| id_tim | nama_tim    |
+--------+-------------+
|      1 | PS Pertamax |
|      2 | Duo United  |
|      3 | Three City  |
|      4 | Quad FC     |
+--------+-------------+

DROP TABLE IF EXISTS `tbl_hasil_pertandingan`;
CREATE TABLE IF NOT EXISTS `tbl_hasil_pertandingan` (
  `id_hasil` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tim_1`  tinyint(3) unsigned NOT NULL,
  `score_1`  tinyint(3) unsigned NOT NULL,
  `tim_2` tinyint(3) unsigned NOT NULL,
  `score_2`  tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id_hasil`)
) ENGINE=MyISAM;

INSERT INTO `tbl_hasil_pertandingan`(`tim_1`,`score_1`,`tim_2`,`score_2`)
VALUES
(1,3,2,2),
(3,0,4,0),
(1,2,3,1),
(2,1,4,2),
(1,2,4,2),
(3,1,2,2);

SELECT * FROM tbl_hasil_pertandingan;
+----------+-------+---------+-------+---------+
| id_hasil | tim_1 | score_1 | tim_2 | score_2 |
+----------+-------+---------+-------+---------+
|        1 |     1 |       3 |     2 |       2 |
|        2 |     3 |       0 |     4 |       0 |
|        3 |     1 |       2 |     3 |       1 |
|        4 |     2 |       1 |     4 |       2 |
|        5 |     1 |       2 |     4 |       2 |
|        6 |     3 |       1 |     2 |       2 |
+----------+-------+---------+-------+---------+

SELECT
  b.nama_tim,
  SUM(main) AS main,
  SUM(a.menang) AS menang,
  SUM(a.seri) AS seri,
  SUM(a.kalah) AS kalah,
  SUM(a.timskor) AS memasukkan,
  SUM(a.lawanskor) AS kemasukan,
  SUM(a.timskor-a.lawanskor) AS selisih_gol,
  SUM(a.menang*3+a.seri) AS nilai
FROM
  (
    (
      SELECT
        COUNT(1) AS main,
        tim_1 AS tim,
        SUM(IF(score_1>score_2,1,0)) AS menang,
        SUM(IF(score_1<score_2,1,0)) AS kalah,
        SUM(IF(score_1=score_2,1,0)) AS seri,
        SUM(score_1) AS timskor,
        SUM(score_2) AS lawanskor
      FROM
        tbl_hasil_pertandingan
      GROUP BY
        tim_1
    )
    UNION
    (
      SELECT
        COUNT(1) AS main,
        tim_2 AS tim,
        SUM(IF(score_2>score_1,1,0)) AS menang,
        SUM(IF(score_2<score_1,1,0)) AS kalah,
        SUM(IF(score_2=score_1,1,0)) AS seri,
        SUM(score_2) AS timskor,
        SUM(score_1) AS lawanskor
      FROM
        tbl_hasil_pertandingan
      GROUP BY
        tim_2
    )
  )a
  JOIN tbl_tim b ON
    b.id_tim=a.tim
GROUP BY
  a.tim
ORDER BY
  nilai DESC,
  selisih_gol DESC;

+-------------+------+--------+------+-------+------------+-----------+-------------+-------+
| nama_tim    | main | menang | seri | kalah | memasukkan | kemasukan | selisih_gol | nilai |
+-------------+------+--------+------+-------+------------+-----------+-------------+-------+
| PS Pertamax |    3 |      2 |    1 |     0 |          7 |         5 |           2 |     7 |
| Quad FC     |    3 |      1 |    2 |     0 |          4 |         3 |           1 |     5 |
| Duo United  |    3 |      1 |    0 |     2 |          5 |         6 |          -1 |     3 |
| Three City  |    3 |      0 |    1 |     2 |          2 |         4 |          -2 |     1 |
+-------------+------+--------+------+-------+------------+-----------+-------------+-------+