我在MySQL数据库中有一个表,我要从中选择时间戳最接近另一个给定时间戳的行。time
是时间戳列(整数UNIX时间戳)。我任意选择了1250710000
。
这是我提出的查询,我想知道是否有更有效的方法可以做到这一点:
SELECT *, ABS(time - 1250710000) AS time_dist FROM table
ORDER BY time_dist ASC LIMIT 1
假设time
已建立索引,则几乎可以免费获得下一条记录:
SELECT * FROM table WHERE time > 1250710000 ORDER BY time LIMIT 1
SELECT *
FROM
(
(SELECT *, ABS(time - 1250710000) AS time_diff FROM table WHERE time > 1250710000 ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT *, ABS(time - 1250710000) AS time_diff FROM table WHERE time < 1250710000 ORDER BY time DESC LIMIT 1)
) AS tmp
ORDER BY time_diff
LIMIT 1
>
和<
代替>=
和<=
,并使用其主ID排除引用记录,以说明共享相同时间戳的记录。
关于mysql - 在MySQL中找到最接近的整数的最有效方法?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1303651/
欢迎光临 OGeek|极客世界-中国程序员成长平台 (http://jike.in/) | Powered by Discuz! X3.4 |