Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 |
- +-------------+------+--------+------+-------+------------+-----------+-------------+-------+
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement