0eTGFPPT

1DROP TABLE IF EXISTS `tbl_tim`;
2CREATE TABLE IF NOT EXISTS `tbl_tim` (
3  `id_tim` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
4  `nama_tim`  varchar(30) NOT NULL,
5  PRIMARY KEY (`id_tim`)
6) ENGINE=MyISAM;
7 
8INSERT INTO `tbl_tim`(`nama_tim`)
9VALUES
10('PS Pertamax'),
11('Duo United'),
12('Three City'),
13('Quad FC');
14
15SELECT * FROM tbl_tim;
16+--------+-------------+
17| id_tim | nama_tim    |
18+--------+-------------+
19|      1 | PS Pertamax |
20|      2 | Duo United  |
21|      3 | Three City  |
22|      4 | Quad FC     |
23+--------+-------------+
24 
25DROP TABLE IF EXISTS `tbl_hasil_pertandingan`;
26CREATE TABLE IF NOT EXISTS `tbl_hasil_pertandingan` (
27  `id_hasil` int(11) unsigned NOT NULL AUTO_INCREMENT,
28  `tim_1`  tinyint(3) unsigned NOT NULL,
29  `score_1`  tinyint(3) unsigned NOT NULL,
30  `tim_2` tinyint(3) unsigned NOT NULL,
31  `score_2`  tinyint(3) unsigned NOT NULL,
32  PRIMARY KEY (`id_hasil`)
33) ENGINE=MyISAM;
34 
35INSERT INTO `tbl_hasil_pertandingan`(`tim_1`,`score_1`,`tim_2`,`score_2`)
36VALUES
37(1,3,2,2),
38(3,0,4,0),
39(1,2,3,1),
40(2,1,4,2),
41(1,2,4,2),
42(3,1,2,2);
43
44SELECT * FROM tbl_hasil_pertandingan; 
45+----------+-------+---------+-------+---------+
46| id_hasil | tim_1 | score_1 | tim_2 | score_2 |
47+----------+-------+---------+-------+---------+
48|        1 |     1 |       3 |     2 |       2 |
49|        2 |     3 |       0 |     4 |       0 |
50|        3 |     1 |       2 |     3 |       1 |
51|        4 |     2 |       1 |     4 |       2 |
52|        5 |     1 |       2 |     4 |       2 |
53|        6 |     3 |       1 |     2 |       2 |
54+----------+-------+---------+-------+---------+
55
56SELECT 
57  b.nama_tim,
58  SUM(main) AS main,
59  SUM(a.menang) AS menang,
60  SUM(a.seri) AS seri,
61  SUM(a.kalah) AS kalah,
62  SUM(a.timskor) AS memasukkan,
63  SUM(a.lawanskor) AS kemasukan,
64  SUM(a.timskor-a.lawanskor) AS selisih_gol,
65  SUM(a.menang*3+a.seri) AS nilai
66FROM
67  (
68    (
69      SELECT 
70        COUNT(1) AS main,
71        tim_1 AS tim,
72        SUM(IF(score_1>score_2,1,0)) AS menang,
73        SUM(IF(score_1<score_2,1,0)) AS kalah,
74        SUM(IF(score_1=score_2,1,0)) AS seri,
75        SUM(score_1) AS timskor,
76        SUM(score_2) AS lawanskor
77      FROM 
78        tbl_hasil_pertandingan
79      GROUP BY 
80        tim_1
81    )    
82    UNION
83    (
84      SELECT 
85        COUNT(1) AS main,
86        tim_2 AS tim,
87        SUM(IF(score_2>score_1,1,0)) AS menang,
88        SUM(IF(score_2<score_1,1,0)) AS kalah,
89        SUM(IF(score_2=score_1,1,0)) AS seri,
90        SUM(score_2) AS timskor,
91        SUM(score_1) AS lawanskor
92      FROM 
93        tbl_hasil_pertandingan
94      GROUP BY 
95        tim_2
96    )
97  )a
98  JOIN tbl_tim b ON
99    b.id_tim=a.tim
100GROUP BY 
101  a.tim
102ORDER BY   
103  nilai DESC, 
104  selisih_gol DESC;                
105
106+-------------+------+--------+------+-------+------------+-----------+-------------+-------+
107| nama_tim    | main | menang | seri | kalah | memasukkan | kemasukan | selisih_gol | nilai |
108+-------------+------+--------+------+-------+------------+-----------+-------------+-------+
109| PS Pertamax |    3 |      2 |    1 |     0 |          7 |         5 |           2 |     7 |
110| Quad FC     |    3 |      1 |    2 |     0 |          4 |         3 |           1 |     5 |
111| Duo United  |    3 |      1 |    0 |     2 |          5 |         6 |          -1 |     3 |
112| Three City  |    3 |      0 |    1 |     2 |          2 |         4 |          -2 |     1 |
113+-------------+------+--------+------+-------+------------+-----------+-------------+-------+