mysql如何查询上一条信息,如何在MySQL中使用单个查询查找上一条和下一条记录？...

I have a database, and I want to find out the previous and next record ordered by ID, using a single query. I tried to do a union but that does not work. :(

SELECT * FROM table WHERE `id` > 1556 LIMIT 1

UNION

SELECT * FROM table WHERE `id` <1556 ORDER BY `product_id` LIMIT 1

Any ideas?

Thanks a lot.

解决方案

You need to change up your ORDER BY:

SELECT * FROM table WHERE `id` > 1556 ORDER BY `id` ASC LIMIT 1

UNION

SELECT * FROM table WHERE `id` < 1556 ORDER BY `id` DESC LIMIT 1

This ensures that the id field is in the correct order before taking the top result.

You can also use MIN and MAX:

SELECT

*

FROM

table

WHERE

id = (SELECT MIN(id) FROM table where id > 1556)

OR id = (SELECT MAX(id) FROM table where id < 1556)

It should be noted that SELECT * is not recommended to have in production code, though, so name your columns in your SELECT statement.