如何在MySQL中使用单个查询查找上一条和下一条记录？
您可以使用UNION获取MySQL中的上一个和下一个记录。
语法如下
(select *from yourTableName WHERE yourIdColumnName > yourValue ORDER BY yourIdColumnName ASC LIMIT 1) UNION (select *from yourTableName WHERE yourIdColumnName < yourValue ORDER BY yourIdColumnName DESC LIMIT 1);
为了理解这个概念，让我们创建一个表。创建表的查询如下
mysql> create table previousAndNextRecordDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(30) - > );
使用insert命令在表中插入一些记录。
查询如下
mysql> insert into previousAndNextRecordDemo(Name) values('John');
mysql> insert into previousAndNextRecordDemo(Name) values('Sam');
mysql> insert into previousAndNextRecordDemo(Name) values('Carol');
mysql> insert into previousAndNextRecordDemo(Name) values('Bob');
mysql> insert into previousAndNextRecordDemo(Name) values('Larry');
mysql> insert into previousAndNextRecordDemo(Name) values('David');
mysql> insert into previousAndNextRecordDemo(Name) values('Ramit');
mysql> insert into previousAndNextRecordDemo(Name) values('Maxwell');
mysql> insert into previousAndNextRecordDemo(Name) values('Mike');
mysql> insert into previousAndNextRecordDemo(Name) values('Robert');
mysql> insert into previousAndNextRecordDemo(Name) values('Chris');
mysql> insert into previousAndNextRecordDemo(Name) values('James');
mysql> insert into previousAndNextRecordDemo(Name) values('Jace');使用select语句显示表中的所有记录。
查询如下
mysql> select *from previousAndNextRecordDemo;
以下是输出
+----+---------+ | Id | Name | +----+---------+ | 1 | John | | 2 | Sam | | 3 | Carol | | 4 | Bob | | 5 | Larry | | 6 | David | | 7 | Ramit | | 8 | Maxwell | | 9 | Mike | | 10 | Robert | | 11 | Chris | | 12 | James | | 13 | Jace | +----+---------+ 13 rows in set (0.00 sec)
这是使用带有UNION的单个查询获取上一条和下一条记录的查询
mysql> (select *from previousAndNextRecordDemo WHERE Id > 8 ORDER BY Id ASC LIMIT 1) - > UNION - > (select *from previousAndNextRecordDemo WHERE Id < 8 ORDER BY Id DESC LIMIT 1);
以下是输出
+----+-------+ | Id | Name | +----+-------+ | 9 | Mike | | 7 | Ramit | +----+-------+ 2 rows in set (0.03 sec)