设置一个rank为之间戳,通过选择上移,就是将本记录与上一条记录rank值交换,下移就是将本条记录与下一条记录rank值交换,置顶就是将本记录与rank值最小的记录交换
1 | SELECT * FROM user ORDER BY rank ASC |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | + ----+------+---------+ | 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 | + ----+------+---------+ |
1 | select * from user where id <3 or id=( SELECT MIN (id) from user ) order by id desc limit 1 |
1 2 3 4 5 6 | + ----+------+--------+ | ID | rank | name | + ----+------+--------+ | 2 | 2 | admin2 | + ----+------+--------+ 1 row in set (0.00 sec) |
1 2 3 4 5 6 7 | 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) |
1 2 3 4 5 6 7 | 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) |
1 2 3 4 5 6 7 | 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) |
联系客服