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