test, MySQL - rextester

test

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';

CREATE TABLE IF NOT EXISTS stats_players (
    idPlayerSteam BIGINT UNSIGNED PRIMARY KEY,
    name VARCHAR(255));
    
    CREATE TABLE IF NOT EXISTS stats_events (
    idEvent INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    idPlayerSteam BIGINT UNSIGNED,
    type VARCHAR(50),
    dateOccurs DATETIME,
    idPlayerSteamVictim BIGINT UNSIGNED);
    
    INSERT IGNORE INTO stats_players (idPlayerSteam, name) VALUES
(1,'bob'), (2,'tom'),(3,'robby'),(4,'dany'),(5,'john');
TRUNCATE stats_events;
INSERT IGNORE INTO stats_events (idPlayerSteam,type,dateOccurs,idPlayerSteamVictim) VALUES
(1,'PVP','2016-05-05 12:12:12',3),
(1,'PVP','2016-05-05 12:15:12',3),
(1,'PVP','2016-05-05 12:16:12',3),
(1,'PVP','2016-05-05 12:17:12',3),
(2,'PVP','2016-05-05 13:12:12',3),
(2,'PVP','2016-05-05 12:19:12',3),
(2,'PVP','2016-05-05 12:20:12',3),
(3,'PVP','2016-05-06 12:12:10',2),
(3,'PVP','2016-05-06 12:12:11',1),
(4,'PVP','2016-05-06 12:12:12',5),
(4,'PVP','2016-05-06 12:12:12',5),
(4,'PVP','2016-05-06 12:12:12',5);

SELECT * FROM stats_players;

SELECT sp.*, (
  SELECT idPlayerSteam FROM stats_events se WHERE se.idPlayerSteamVictim = sp.idPlayerSteam
  GROUP BY idPlayerSteamVictim ORDER BY COUNT(*) DESC, dateOccurs DESC LIMIT 1
) 'nemesis_id',
(
  SELECT (SELECT name FROM stats_players spx WHERE spx.idPlayerSteam = se.idPlayerSteam) FROM stats_events se WHERE se.idPlayerSteamVictim = sp.idPlayerSteam
  GROUP BY idPlayerSteamVictim ORDER BY COUNT(*) DESC, dateOccurs DESC LIMIT 1
) 'nemesis_name',(
  SELECT COUNT(*) FROM stats_events se WHERE se.idPlayerSteamVictim = sp.idPlayerSteam AND idPlayerSteam = nemesis_id
  GROUP BY idPlayerSteamVictim ORDER BY COUNT(*) DESC, dateOccurs DESC LIMIT 1
) 'kills' FROM stats_players sp


run \| edit \| history \| help	0