表
--------------------------------------------------------------
id name sort
1 aa 1
2 bb 2
3 cc 3
4 dd 4
5 ee 5
6 ff 6
7 gg 7--------------------------------------------------------
如果我要将sort 7 变成 sort 2 ,2变成3,4变成4以此推怎样实现用最少的update 语句,来完成。
????????????????
---------------------------------------------------------------
--------------------------------------------------------------
id name sort
1 aa 1
2 bb 2
3 cc 3
4 dd 4
5 ee 5
6 ff 6
7 gg 7--------------------------------------------------------
如果我要将sort 7 变成 sort 2 ,2变成3,4变成4以此推怎样实现用最少的update 语句,来完成。
????????????????
---------------------------------------------------------------
+------+------+------+
| id | name | sort |
+------+------+------+
| 1 | aa | 1 |
| 2 | bb | 2 |
| 3 | cc | 3 |
| 4 | dd | 4 |
| 5 | ee | 5 |
| 6 | ff | 6 |
| 7 | gg | 7 |
+------+------+------+
7 rows in set (0.00 sec)mysql> update t_wang_830608500
-> set sort=if(sort=7,2,sort+1)
-> where id>1;
Query OK, 6 rows affected (0.08 sec)
Rows matched: 6 Changed: 6 Warnings: 0mysql> select * from t_wang_830608500;
+------+------+------+
| id | name | sort |
+------+------+------+
| 1 | aa | 1 |
| 2 | bb | 3 |
| 3 | cc | 4 |
| 4 | dd | 5 |
| 5 | ee | 6 |
| 6 | ff | 7 |
| 7 | gg | 2 |
+------+------+------+
7 rows in set (0.00 sec)mysql>
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html