如何在MySQL中使用单个查询查找上一条和下一条记录? [英] How to find the previous and next record using a single query in MySQL?

问题描述

我有一个数据库，我想使用一个查询找出按ID排序的上一条和下一条记录.我试图建立一个工会，但这是行不通的. :(

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.

推荐答案

您需要更改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

这可以确保在获得最高结果之前，id字段的顺序正确.

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

您还可以使用MIN和MAX:

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)

应该注意的是，尽管不建议在生产代码中使用SELECT *，所以请在SELECT语句中命名您的列.

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

这篇关于如何在MySQL中使用单个查询查找上一条和下一条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！