mysql - 如何在一个查询中获取每个用户特定日期之前的第一行和之后的行？

Question

我有一个表 online_users_changes，每当用户在线/空闲/离线时，我都会在其中存储。

CREATE TABLE online_users_changes(id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, timestamp TIMESTAMP, status TEXT);
INSERT INTO online_users_changes(user_id, timestamp, status) VALUES 
(1, "2014-07-05 9:00", "idle"),
(1, "2014-07-05 12:00", "online"),
(1, "2014-07-05 15:00", "offline"),
(2, "2014-07-05 7:00", "offline"),
(2, "2014-07-05 13:00", "online"),
(2, "2014-07-05 14:00", "offline");

我想创建图表，显示用户在特定时间段内的状态。所以我需要获取这段时间的所有更改+之前的最新更改。

SELECT * 
FROM online_users_changes 
WHERE timestamp >= :start and timestamp < :end
ORDER BY user_id, timestamp

此查询仅返回 :start 和 :end 之间的更改，但我还需要每个用户在 :start 之前的最新更改。

如何获取我想要的数据？

http://sqlfiddle.com/#!2/8147a9/2

例如这个查询

SELECT *
FROM online_users_changes 
WHERE timestamp BETWEEN "2014-07-05 11:00" and "2014-07-05 22:00" 
ORDER BY user_id, timestamp;

返回

| USER_ID |                   TIMESTAMP |  STATUS |
|---------|-----------------------------|---------|
|       1 | July, 05 2014 12:00:00+0000 |  online |
|       1 | July, 05 2014 15:00:00+0000 | offline |
|       2 | July, 05 2014 13:00:00+0000 |  online |
|       2 | July, 05 2014 14:00:00+0000 | offline |

但我想获得这样的东西。

| USER_ID |                   TIMESTAMP |  STATUS |
|---------|-----------------------------|---------|
|       1 | July, 05 2014 09:00:00+0000 |    idle |
|       1 | July, 05 2014 12:00:00+0000 |  online |
|       1 | July, 05 2014 15:00:00+0000 | offline |
|       2 | July, 05 2014 07:00:00+0000 | offline |
|       2 | July, 05 2014 13:00:00+0000 |  online |
|       2 | July, 05 2014 14:00:00+0000 | offline |

Answer 1

"给定日期之前的最近事件"= "给定日期之前发生的第一个事件，按时间倒序排序时"=

SELECT * FROM online_users_changes
WHERE timestamp < "your date and time here"
ORDER BY timestamp DESC
LIMIT 1

然后 UNION 此行与主查询。

请注意，将 ORDER BY 子句与 UNION 一起使用有点棘手，您需要在正确的位置加上括号(manual pages 拥有所有)。