更新列，使其包含行位置

Question

这是content表：

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

现在，我想实现上移，下移，上移和下移功能。我需要做的就是用数字填充“位置”列：

ContentID | CategoryID | Position
=================================
1         | 1          | 1
2         | 1          | 2
3         | 1          | 3
4         | 2          | 1
5         | 2          | 2
6         | 2          | 3

是否可以通过MySQL中的单个查询来实现？就像是：

UPDATE content
SET Position = <ROW_NUMBER>
WHERE CategoryID = 1
ORDER BY Position

UPDATE content
SET Position = <ROW_NUMBER>
WHERE CategoryID = 2
ORDER BY Position

Answer 1

这应该工作

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;

您可以在这样的一条语句中做到这一点

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,
(
  select @row_number:=0
) as rowNumberInit
set position=table_position.new_position
where table_position.ContentID=content.ContentID;