mysql 如何从时间戳字段获取此结果

Question

我有一个类似这样的表，其中保存用户登录和注销时间:

----------------------------------------------
name        |date       |timestamp   |action
----------------------------------------------
Philip      |2017-05-08 |1494225926  |login
Anna        |2017-05-08 |1494229108  |login
Laura       |2017-05-08 |1494232336  |login
Gerry       |2017-05-08 |1494232431  |login
Anna        |2017-05-08 |1494243781  |logout
Anna        |2017-05-08 |1494246660  |login
Laura       |2017-05-08 |1494247039  |logout
Philip      |2017-05-08 |1494248271  |logout
Laura       |2017-05-08 |1494249980  |login
Philip      |2017-05-08 |1494252177  |login
Laura       |2017-05-08 |1494255182  |logout
Laura       |2017-05-08 |1494256117  |login
Anna        |2017-05-08 |1494259279  |logout
Philip      |2017-05-08 |1494262771  |logout
Philip      |2017-05-08 |1494262775  |login
Gerry       |2017-05-08 |1494262821  |logout
Laura       |2017-05-08 |1494262822  |logout
Philip      |2017-05-08 |1494262886  |logout

我正在尝试创建一个查询，告诉我谁在时间戳指定的时间(即 1494248400)登录..有什么想法吗？

Answer 1

您可以使用NOT IN，例如:

SELECT * FROM test WHERE action = 'login' and `timestamp` <= 1494248400
AND name NOT IN (
    SELECT name FROM test WHERE action = 'logout' and `timestamp` <= 1494248400
)

这是SQL Fiddle 。

更新

要获取所有注销但重新登录的用户，可以使用带有条件的GROUP BY，例如:

SELECT * FROM (
 SELECT name, max(`timestamp`) as ts
 FROM test WHERE action = 'login' and `timestamp` <= 1494248400
 GROUP BY name ) t1
WHERE t1.name NOT IN (
   SELECT name FROM test WHERE action = 'logout' 
   AND `timestamp` <= 1494248400 AND timestamp >= t1.ts
  );

这是更新后的SQL Fiddle 。