mysql选择上一条、下一条数据记录，排序上移、下移、置顶

mysql选择上一条、下一条数据记录，排序上移、下移、置顶_MySQL

php中文网

发布： 2016-06-01 13:01:15

原创

2408人浏览过

1.功能需要

完成列表排序上移，下移，置顶功能！效果如下图所示：

2设置思路

设置一个rank为之间戳，通过选择上移，就是将本记录与上一条记录rank值交换，下移就是将本条记录与下一条记录rank值交换，置顶就是将本记录与rank值最小的记录交换

3.表数据结构

选择mysql记录上一条，下一条sql语句

JoyPix AI

轻松制作AI视频、AI数字人，支持文生视频、声音克隆

243

查看详情

SELECT * FROM user ORDER BY rank ASC

登录后复制

+----+------+---------+
| ID | rank | name    |
+----+------+---------+
|  1 |    1 | admin1  |
|  2 |    2 | admin2  |
|  3 |    3 | admin3  |
|  4 |    4 | admin4  |
|  5 |    5 | admin5  |
|  6 |    6 | admin6  |
|  7 |    7 | admin7  |
|  8 |    8 | admin8  |
|  9 |    9 | admin9  |
| 10 |   10 | admin10 |
| 11 |   11 | admin11 |
| 12 |   12 | admin12 |
+----+------+---------+

登录后复制

4.选择上一行

select * from user where id <3 or id=(SELECT MIN(id) from user) order by id desc limit 1

登录后复制

+----+------+--------+
| ID | rank | name   |
+----+------+--------+
|  2 |    2 | admin2 |
+----+------+--------+
1 row in set (0.00 sec)

登录后复制

5.当id=1时

mysql> select * from user where id <1 or id=(SELECT MIN(id) from user) order by id desc limit 1;
+----+------+--------+
| ID | rank | name   |
+----+------+--------+
|  1 |    1 | admin1 |
+----+------+--------+
1 row in set (0.00 sec)

登录后复制

6.选择下一行

mysql> select * from user where id > 3 OR ID=(SELECT MAX(ID) FROM user) ORDER BY id ASC LIMIT 1;
+----+------+--------+
| ID | rank | name   |
+----+------+--------+
|  4 |    4 | admin4 |
+----+------+--------+
1 row in set (0.00 sec)

登录后复制

7.当id为最大值时

mysql> select * from user where id > 12 OR ID=(SELECT MAX(ID) FROM user) ORDER BY id ASC LIMIT 1;
+----+------+---------+
| ID | rank | name    |
+----+------+---------+
| 12 |   12 | admin12 |
+----+------+---------+
1 row in set (0.00 sec)

登录后复制