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

2014-12-25 09:22:47     3 个评论   来源：小哈-whzhaochao  

收藏   

我要投稿

1.功能需要

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

2设置思路

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

3.表数据结构

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

?

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 | +----+------+---------+

4.选择上一行

?

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)

5.当id=1时

?

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)

6.选择下一行

?

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)

7.当id为最大值时

?

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)