这是内容表：
- 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;
