如何获得带有“加入”或“离开”值的最新行，并对它们进行排序？

I have a table that tracks join and leave events from players on a game and each of it's servers. The problem I have is, how do I know whose offline and who's online?

This is how my table is structured, and some sample values:

CREATE TABLE `b_rbx_connections` (
  `id` int(11) NOT NULL,
  `server` varchar(48) NOT NULL,
  `place` int(11) NOT NULL,
  `player` int(11) NOT NULL,
  `type` enum('join','leave') NOT NULL,
  `followed` int(11) DEFAULT NULL COMMENT '0/null=none',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `b_rbx_connections` (`id`, `server`, `place`, `player`, `type`, `followed`, `time`) VALUES
(1, '83889ac4-eaf9-45af-8b9d-fa1b14dba952', 1253620452, 50180001, 'join', NULL, '2018-02-10 06:04:30'),
(2, '83889ac4-eaf9-45af-8b9d-fa1b14dba952', 1253620452, 50180001, 'leave', NULL, '2018-02-10 06:32:27'),
(3, '9f1aabe0-e314-46f1-bb81-c45ea70fdae9', 1253620452, 50180001, 'join', NULL, '2018-02-11 22:54:53');

Each time a player joins a row is inserted with the server id, place id, whether it was a join or leave, and the time of the event.

This is what I've tried so far, but it only works if each player has only joined/left once:

$q = "SELECT DISTINCT player FROM `b_rbx_connections` WHERE place='$placeId' AND type='join' AND time <= '$timestamp' ORDER BY `time` DESC";
$j = $db->query($q);
$q = "SELECT DISTINCT player FROM `b_rbx_connections` WHERE place='$placeId' AND type='leave' AND time <= '$timestamp' ORDER BY `time` DESC";
$l = $db->query($q);

$this->players = $j->fetchAll(PDO::FETCH_COLUMN, 0);
$this->offline_players = $l->fetchAll(PDO::FETCH_COLUMN, 0);
$this->online_players = array_diff($this->players, $this->offline_players);

This outputs:

{
    "players": [
        "50180001"
    ],
    "offline_players": [
        "50180001"
    ],
    "online_players": [

    ]
}

But it should be.

{
    "players": [
        "50180001"
    ],
    "offline_players": [

    ],
    "online_players": [
        "50180001"

    ]
}

Note: A place is a game id, a server is the instance id of a server in that game, and a player is the id of that player.

You should group the player within a search to get the "latest" status of each player. Then use that new array to parse out the results you want.

SELECT  *
FROM (Select * FROM `b_rbx_connections` order by time desc) as results
GROUP BY player

Check out http://sqlfiddle.com/#!9/fd03cb/3/0, where I added more players to test the query.

select p.player, (
    select c.type
    from b_rbx_connections c
    where c.player = p.player
      and c.place = 1253620452
    order by c.time desc
    limit 1
) as type
from b_rbx_players p

This will return all players and the type of the last row in the connections table for a given place. If the player has no entry, the type will be NULL (which can be considered offline).

Now use a PHP loop to assign the players to the different arrays.