这是内容表：
ContentID | CategoryID | Position | Other1 | Other2 =================================================== 1 | 1 | NULL | abcd | efgh 2 | 1 | NULL | abcd | efgh 3 | 1 | NULL | abcd | efgh 4 | 2 | NULL | abcd | efgh 5 | 2 | NULL | abcd | efgh 6 | 2 | NULL | abcd | efgh
这些是我将要运行的查询：
SELECT ContentID FROM content WHERE CategoryID = 1 ORDER BY Position SELECT ContentID FROM content WHERE CategoryID = 2 ORDER BY Position
现在我想实现向上移动,向下移动,向上移动到底部功能的内容.我需要做的就是用数字填充Position列：
ContentID | CategoryID | Position ================================= 1 | 1 | 1 2 | 1 | 2 3 | 1 | 3 4 | 2 | 1 5 | 2 | 2 6 | 2 | 3
UPDATE content SET Position = <ROW_NUMBER> WHERE CategoryID = 1 ORDER BY Position UPDATE content SET Position = <ROW_NUMBER> WHERE CategoryID = 2 ORDER BY Position
这应该工作
update content,( select @row_number:=ifnull(@row_number,0)+1 as new_position,ContentID from content where CategoryID=1 order by position ) as table_position set position=table_position.new_position where table_position.ContentID=content.ContentID;
但是我首先应用这个来取消设置用户定义的变量
set @row_number:=0;
由Mchl添加：
你可以在这样的一个声明中做到这一点
update content,ContentID from content where CategoryID=1 order by position ) as table_position,( select @row_number:=0 ) as rowNumberInit set position=table_position.new_position where table_position.ContentID=content.ContentID;
