mysql - 在MySQL中，如何查询 "last inserted row equals"？

Question

mysql> desc accounts_users;
+------------------------+--------------+------+-----+---------+----------------+
| Field                  | Type         | Null | Key | Default | Extra          |
+------------------------+--------------+------+-----+---------+----------------+
| id                     | int(11)      | NO   | PRI | NULL    | auto_increment |
| login_at               | datetime     | YES  |     | NULL    |                |
| logout_at              | datetime     | YES  |     | NULL    |                |
+------------------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc rooms_events;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)      | NO   | MUL | NULL    |                |
| event      | varchar(255) | NO   | MUL | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

当用户执行操作时，我将“事件”行添加到 rooms_events 表中。事件列可以是“创建”或“销毁”。没有唯一的列。

我要查询:

Find all unique user_ids whose latest event is "create" AND hasn't logged in (login_at) for 24 hours.

如何执行这样的查询？

Answer 1

我想说可以这样查询:

SELECT DISTINCT U.id
FROM    accounts_users U
INNER JOIN rooms_events E ON U.id = E.user_id
WHERE   U.login_at < DATE_SUB(NOW(),INTERVAL 24 HOUR)
        AND E.created_at IN (
            SELECT MAX(created_at)
            FROM rooms_events
            GROUP BY user_id
           )
        AND E.`event` = 'create';

我不能 100% 确定子查询在所有可能的情况下都能完成其工作，但根据一些测试数据，它在我的机器上工作。