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

雨点打透心脏的1/2处 2022-08-06 05:12 237阅读 0赞

### 1.功能需要 ###

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

![1419413477_3219.gif][]

![1419491046_2383.gif][]

### 2设置思路 ###

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

### 3.表数据结构 ###

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

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)

[1419413477_3219.gif]: https://img-my.csdn.net/uploads/201412/24/1419413477_3219.gif
[1419491046_2383.gif]: https://img-my.csdn.net/uploads/201412/25/1419491046_2383.gif