极客教程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
为了理解这个概念，让我们创建一个表。创建表的查询如下：
mysql> create table previousAndNextRecordDemo
- > (
- > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
- > Name varchar(30)
- > );
Query OK, 0 rows affected (1.04 sec)Mysql
使用插入命令在表中插入一些记录。
查询如下：
mysql> insert into previousAndNextRecordDemo(Name) values('John');
Query OK, 1 row affected (0.17 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Sam');
Query OK, 1 row affected (0.15 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Bob');
Query OK, 1 row affected (0.17 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Larry');
Query OK, 1 row affected (0.20 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('David');
Query OK, 1 row affected (0.14 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Ramit');
Query OK, 1 row affected (0.12 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Maxwell');
Query OK, 1 row affected (0.15 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Mike');
Query OK, 1 row affected (0.14 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Robert');
Query OK, 1 row affected (0.19 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Chris');
Query OK, 1 row affected (0.10 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('James');
Query OK, 1 row affected (0.16 sec)
mysql> insert into previousAndNextRecordDemo(Name) values('Jace');
Query OK, 1 row affected (0.15 sec)Mysql
使用select语句显示表中的所有记录。
查询如下：
mysql> select *from previousAndNextRecordDemo;Mysql
以下是输出结果：
+----+---------+
| 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)Mysql
以下是使用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);Mysql
以下是输出结果：
+----+-------+
| Id | Name |
+----+-------+
| 9 | Mike |
| 7 | Ramit |
+----+-------+
2 rows in set (0.03 sec)Mysql
阅读更多：MySQL 教程