Read 117 times | Created 2013-10-29 14:09:58 | Updated 2013-10-29 14:09:58 | | |

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');

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 
  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;