需求场景：

当表中的一个字段存储当前记录的顺序或者排名时候，它需要进行上移或者下移，或者直接和某个记录交换值的时候，通常可以立刻想到以下的SQL操作语句。      

CREATE TABLE coordinatePt_Table(nID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',nIndex SMALLINT UNSIGNED NOT NULL COMMENT '顺序',Longitude DOUBLE COMMENT '经度',Latitude DOUBLE COMMENT '纬度',PRIMARY KEY(nID));DELETE FROM coordinatePt_Table WHERE nID < 100;INSERT INTO coordinatePt_Table VALUES(NULL, 0, 30.10, 110.0),(NULL, 1, 34.10, 115.0);SELECT * FROM coordinatePt_Table LIMIT 100;SET @nTemp1 = 0;SET @nTemp2 = 0;SELECT nIndex INTO @nTemp1 FROM coordinatePt_Table WHERE nID = 1;SELECT nIndex INTO @nTemp2 FROM coordinatePt_Table WHERE nID = 2;UPDATE coordinatePt_Table SET nIndex = @nTemp2 WHERE nID = 1;UPDATE coordinatePt_Table SET nIndex = @nTemp1 WHERE nID = 2;

肯定有更简单的方式实现需求。

UPDATEcoordinatePt_Table AS T1 JOINcoordinatePt_Table AS T2 ON (T1.nID = 1 AND T2.nID = 2)SET T1.nIndex = T2.nIndex, T2.nIndex = T1.nIndex;

 

MYSQL查询语句：