程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 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)




  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved