求mysql取某条件下id及前一条，后一条数据?

Question

数据表结构如下
id,date(时间戳),categoryid(类型id)

数据查询
select id,categoryid,date from table order by categoryid asc,date desc
以上查询会返回n条数据，然后通过程序遍历可以取某id前一id,及后一id,但是有的时候可能会查询到上万条数据，相对来说效率不高，有没有mysq查询最高效的其它方法？

Answer 1

类似博客分页？上一篇下一篇？

假设预知当前中间数据ID为 x

(select * from table where id < x order by id desc limit 1)union all(select * from table where id > x order by id asc limit 1);

Answer 2

之前写过，在一亿设备推送记录里（每分钟一次，长达两年），找哪些设备超过一天没反应。

里面有用到类似你说的，《不断找 24 小时内最早记录，找不到则 24 小时外最晚记录》。

效率上，浏览器里都能 0.2 秒，查 7W+ 次。本机只需 0.1 秒。

你可以参考下，可能有帮助。

Answer 3

在MySQL中，为了高效地获取某条记录的前一条和后一条记录，可以使用LEAD和LAG窗口函数（在MySQL 8.0及更高版本中可用）。这些函数允许你访问结果集中的前一行和后一行的值，而无需对整个结果集进行排序和遍历。

以下是一个使用LEAD和LAG函数来获取指定id的前一条和后一条记录的示例查询：

SELECT     id,    categoryid,    date,    LAG(id) OVER (ORDER BY categoryid ASC, date DESC) AS prev_id,    LAG(date) OVER (ORDER BY categoryid ASC, date DESC) AS prev_date,    LEAD(id) OVER (ORDER BY categoryid ASC, date DESC) AS next_id,    LEAD(date) OVER (ORDER BY categoryid ASC, date DESC) AS next_dateFROM     your_table_nameORDER BY     categoryid ASC,     date DESC;

在这个查询中，LAG函数用于获取前一条记录的id和date，而LEAD函数用于获取后一条记录的id和date。OVER子句定义了窗口函数的排序顺序，这里我们按照categoryid升序和date降序进行排序。

请注意，你需要将your_table_name替换为你的实际表名。

这个查询将返回整个排序后的结果集，并且对于每一行都包含了前一条和后一条记录的id和date。如果你只对某个特定id感兴趣，你可以在这个查询的基础上添加一个WHERE子句来过滤结果。例如，如果你想要获取id = 123的前一条和后一条记录，你可以这样做：

SELECT     id,    categoryid,    date,    prev_id,    prev_date,    next_id,    next_dateFROM (    SELECT         id,        categoryid,        date,        LAG(id) OVER (ORDER BY categoryid ASC, date DESC) AS prev_id,        LAG(date) OVER (ORDER BY categoryid ASC, date DESC) AS prev_date,        LEAD(id) OVER (ORDER BY categoryid ASC, date DESC) AS next_id,        LEAD(date) OVER (ORDER BY categoryid ASC, date DESC) AS next_date    FROM         your_table_name    ORDER BY         categoryid ASC,         date DESC) AS subqueryWHERE     id = 123;

这个查询首先执行内部子查询来获取整个结果集以及前一条和后一条记录的id和date，然后在外部查询中添加一个WHERE子句来过滤出id = 123的那一行。