Mysql:如何找到当前在房间里的用户？

Question

我有一个表entrances，它记录了用户进入房间和离开房间的时间。类似的东西:

user   |   action    |    time
-------------------------------------------
Ivan   |   in        |  2016-08-28 12:00:00 
John   |   in        |  2016-08-28 12:00:01
Ann    |   in        |  2016-08-28 12:00:02
Ivan   |   out       |  2016-08-28 12:00:03
Ivan   |   in        |  2016-08-28 12:00:04
Ann    |   out       |  2016-08-28 12:00:05
Ivan   |   out       |  2016-08-28 12:00:06
Mike   |   in        |  2016-08-28 12:00:07
John   |   out       |  2016-08-28 12:00:08
Ann    |   out       |  2016-08-18 12:00:09
John   |   in        |  2016-08-18 12:00:10
John   |   out       |  2016-08-18 12:00:11
Ann    |   in        |  2016-08-18 12:00:12

用户操作是独立的。唯一已知的是，第一个操作始终是in，并且用户不能in两次而不是out(和反向)。

我的目标是找到当前在房间里的所有用户。

我有两个想法:

1. 选择最近in之后没有out的用户
2. 选择计数in 的用户更多计数out

如何在mysql上实现？或者有什么其他想法？

用于测试的SQL:

CREATE TABLE `entrances` (
  `id` int(11) NOT NULL,
  `user` varchar(10) COLLATE utf8_bin NOT NULL,
  `action` varchar(3) COLLATE utf8_bin NOT NULL,
  `time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `entrances` (`id`, `user`, `action`, `time`) VALUES
(1, 'Ivan', 'in', '2016-08-28 12:00:00'),
(2, 'John', 'in', '2016-08-28 12:00:01'),
(3, 'Ann', 'in', '2016-08-28 12:00:02'),
(4, 'Ivan', 'out', '2016-08-28 12:00:03'),
(5, 'Ivan', 'in', '2016-08-28 12:00:04'),
(6, 'Ann', 'out', '2016-08-28 12:00:05'),
(7, 'Ivan', 'out', '2016-08-28 12:00:06'),
(8, 'Mike', 'in', '2016-08-28 12:00:07'),
(9, 'John', 'out', '2016-08-28 12:00:08'),
(10, 'Ann', 'out', '2016-08-28 12:00:09'),
(11, 'John', 'in', '2016-08-28 12:00:10'),
(12, 'John', 'out', '2016-08-28 12:00:11'),
(13, 'Ann', 'in', '2016-08-28 12:00:12');
ALTER TABLE `entrances` ADD PRIMARY KEY (`id`);

Answer 1

1. 首先在子查询中获取每个用户的最后一个 Action time
2. 然后针对该子查询加入每个用户
的最后一条记录
3. 然后仅获取那些具有 action = in 和 where 子句的记录

像这样

select e.*
from entrances e
join
(
   select user, max(time) as mtime
   from entrances
   group by user
) t on t.user = e.user 
   and t.mtime = e.time
where e.action = 'in'