mysql選擇上一條、下一條資料記錄，排序上移、下移、置頂，mysql下一條

mysql選擇上一條、下一條資料記錄，排序上移、下移、置頂，mysql下一條
1.功能需要自動完成清單排序上移，下移，置頂功能！效果如所示：
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)