这是 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
现在我想实现向上移动，向下移动，移动到顶部并移动到底部函数以获取内容。我需要做的就是用数字填充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
这应该工作
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,
(
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;
这是适合我的解决方案（希望它可以帮助某人）：
-- The following query re-populates the "Position" column with sequential numbers so that:
-- a) sequence is reset to 1 for each "group"
-- b) sequence is based on row number relative to each group depending on how ORDER BY is specified
-- c) sequence does not disturb the original order but
-- c.a) fixes NULLs so that they are moved to top
-- c.b) fixes duplicate position values depending on how ORDER BY is specified
-- ContentID is the primary key
-- CategoryID is a foreign key
-- Position column contains relative position of a record
SET @current_group = NULL;
SET @current_count = NULL;
UPDATE
content
SET Position = CASE
WHEN @current_group = CategoryID THEN @current_count := @current_count + 1
WHEN @current_group := CategoryID THEN @current_count := 1
END
ORDER BY CategoryID, Position -- <Column 3>, <Column 4>, ...
我认为，当您对表执行某些操作时，始终运行其他查询将非常繁琐。我会 创建 一个 触发 每次要在表中插入/更新某些内容时都会触发。
在你的情况下，一个 BEFORE UPDATE 和 BEFORE INSERT 触发器是可取的。如果您还想在删除etntry后保持清洁，请添加 AFTER DELETE 触发。
初始：
UPDATE content as uc
SET Position = (
SELECT count(*)
FROM content as sc
WHERE sc.CategoryId = uc.CategoryId AND sc.Position is not null)
WHERE uc.Position is null
ORDER BY uc.ContentId
在插入之前：
UPDATE content
SET Position = Position+1
WHERE Position >= newPos AND CategoryId = newCat