MySQL选择时间戳最接近但不超过给定时间戳的行

我有一张桌子,看起来如下

state_history
+---------------------+-----------+----------------+      +
| state_added_time    | entity_id | state_id       | .... |
+---------------------+-----------+----------------+      |
| 2015-05-15 13:24:22 |         1 |              1 |      |
| 2015-05-15 13:29:44 |         3 |              2 |      |
| 2015-05-15 13:34:26 |         2 |              2 |      |
| 2015-05-15 14:24:28 |         1 |              3 |      |
| 2015-05-15 14:24:30 |         2 |              3 |      |
| 2015-05-15 14:26:32 |         3 |              5 |      |
| 2015-05-15 14:26:34 |         3 |              3 |      |
.......

我的目的是在任何特定时间了解所有实体的状态.例如,如果从应用程序收到的时间戳是2015-05-15 14:25:00,那么预期的输出应该是：

state_history
+---------------------+-----------+----------------+      +
| state_added_time    | entity_id | state_id       | .... |
+---------------------+-----------+----------------+      |
| 2015-05-15 14:24:28 |         1 |              3 |      |
| 2015-05-15 14:24:30 |         2 |              3 |      |
| 2015-05-15 13:29:44 |         3 |              2 |      |
.......

也就是说,要知道在给定时间之前或之后每个实体发生的最后状态变化.状态变化之间的间隔不固定.因此,我不能有2个时间边界,并在它们之间找到行.

我尝试过使用TIMEDIFF但未能获得所需的输出.任何人都可以指导我走的路吗？

编辑：感谢大家的快速回复.我尝试了答案,并注意到在实际数据库上执行时,查询需要花费大量时间来获取行.可能是因为字段entity_id和state_id是其他两个表的外键.
现在知道这是否有任何方法可以提高查询的性能？

最佳答案

您也可以使用变量来完成：

SELECT entity_id,state_added_time,state_id
FROM (
  SELECT state_added_time,state_id,@row_number:= CASE WHEN @entity = entity_id THEN @row_number+1
                             ELSE 1
                        END AS row_number,@entity:=entity_id AS entity_id   
  FROM state_history
  WHERE state_added_time <= '2015-05-15 14:25:00'
ORDER BY entity_id,state_added_time DESC ) t
WHERE t.row_number = 1

每次遇到新的entity_id时都会重置@row_number.在每个entity_id中,@ row_number = 1的值指向最近的记录.

SQL Fiddle Demo

原文链接：https://www.f2er.com/mysql/433502.html

MySQL选择时间戳最接近但不超过给定时间戳的行

猜你在找的MySQL相关文章