vhid1mNh

1SELECT n.name, d.times_kill, d.times_dead, d.kd, d.times_cap, d.points
2FROM
3( SELECT f.player player, f.times_kill, f.times_dead, f.kd, g.times_cap, ((f.points * (7 / 3)) + POW(g.times_cap, 1.5)) points
4FROM
5    ( SELECT k.player player, k.times_kill, d.times_dead, (k.times_kill / d.times_dead) kd, ((k.times_kill / d.times_dead) * k.times_kill) points
6    FROM
7        ( SELECT player, count( * ) times_kill
8        FROM `nexstat`.`dc_events`
9          WHERE event = 'KILL' AND server = '2'
10          GROUP BY player
11          ORDER BY times_kill DESC
12        ) k
13        JOIN
14        ( SELECT player, count( * ) times_dead
15        FROM `nexstat`.`dc_events`
16          WHERE event = 'DEAD' AND server = '2'
17          GROUP BY player
18          ORDER BY times_dead DESC
19        ) d
20        ON d.player = k.player
21        ORDER BY points DESC
22    ) f
23    JOIN
24    ( SELECT player, count( * ) times_cap
25    FROM `nexstat`.`dc_events`
26        WHERE event = 'CAP' AND server = '2'
27        GROUP BY player ORDER BY times_cap DESC
28    ) g
29    ON f.player = g.player
30) d
31JOIN
32( SELECT * FROM `nexstat`.`dc_players` WHERE main = 1
33) n
34ON d.player = n.id
35GROUP BY id
36ORDER BY points DESC
37	
38CREATE TABLE IF NOT EXISTS `dc_events` (
39  `id` int(11) NOT NULL AUTO_INCREMENT,
40  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
41  `player` int(11) NOT NULL,
42  `event` enum('CAP','KILL','DEAD','DROP','PICKUP','CHANGE','JOIN','LEAVE') NOT NULL,
43  `param0` int(11) DEFAULT NULL,
44  `server` int(11) NOT NULL,
45  PRIMARY KEY (`id`),
46  KEY `event` (`event`),
47  KEY `server` (`server`),
48  KEY `player` (`player`)
49) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED AUTO_INCREMENT=1 ;
50
51
52CREATE TABLE IF NOT EXISTS `dc_players` (
53  `id` int(11) NOT NULL AUTO_INCREMENT,
54  `main` tinyint(1) NOT NULL DEFAULT '1',
55  `name` varchar(512) NOT NULL,
56  `website` varchar(512) NOT NULL,
57  `email` varchar(512) NOT NULL,
58  `clan` varchar(512) NOT NULL,
59  `country` varchar(2) NOT NULL,
60  UNIQUE KEY `name` (`name`),
61  KEY `website` (`website`),
62  KEY `email` (`email`),
63  KEY `id` (`id`)
64) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
65	
66SELECT dc_player.name AS player_name,
67       times_kill,
68       times_dead,
69       times_cap,
70       times_kill / times_dead AS kd,
71       ((((times_kill / times_dead) * times_kill) * (7 / 3)) + POW(times_cap, 1.5)) AS points
72FROM (
73    SELECT player,
74           SUM(IF(event='KILL',1,0)) AS times_kill,
75           SUM(IF(event='DEAD',1,0)) AS times_dead,
76           SUM(IF(event='CAP',1,0)) AS times_cap
77    FROM dc_events
78    GROUP BY player
79) AS events
80JOIN dc_player
81  ON dc_player.id = events.player
82ORDER BY points;