如何使用MySQL创建UPDATE语句来设置唯一的顺序值WITHIN记录簇/组？
我有一个包含UniqueID、State（“AL”、“CA”等）、PersonName和StateID列的表。我需要为每个人的StateID编号 * 从每个州的1开始 *。
以下是我的设置：
CREATE TABLE MyData( UniqueID VARCHAR(64), State VARCHAR(64), PersonName VARCHAR(64), StateID INT );
INSERT INTO MyData VALUES ( UUID(), 'CA', 'David', NULL ), ( UUID(), 'CA', 'Ann', NULL ), ( UUID(), 'CA', 'Bob', NULL ),
( UUID(), 'AL', 'Mary', NULL ), ( UUID(), 'AL', 'Tom', NULL ), ( UUID(), 'AL', 'Evelyn', NULL );我想StateID开始在1和增量为每个记录在每个国家，但然后重置为1为每个新的国家。排序不必按字母顺序排列。
预期结果：
someIDvalue AL Mary 1
someIDvalue AL Tom 2
someIDvalue AL Evelyn 3
someIDvalue CA David 1
someIDvalue CA Ann 2
someIDvalue CA Bob 3到目前为止，我可以做到这一点：
UPDATE MyData
INNER JOIN ( SELECT UniqueID, (SELECT @RowNumber := @RowNumber +1) NewStateID
FROM MyData
INNER JOIN ( SELECT @RowNumber := 0 ) InternalVariable
ORDER BY MyData.State ) ReorderedSet ON MyData.UniqueID = ReorderedSet.UniqueID
SET MyData.StateID = ReorderedSet.NewStateID;但是当状态改变时，这不会重置序列。
我不认为使用GROUP是有效的，因为我不需要聚合--只需要知道状态从前一条记录改变的时间。
任何关于这方面的帮助都将不胜感激。
2条答案
按热度按时间pkmbmrz71#
您可以使用以下MySQL查询来设置记录簇/组中的唯一顺序值
SET @row_number:=0; SET @state:=''; UPDATE MyData SET StateID = (@row_number:=CASE WHEN @state=State THEN @row_number+1 ELSE 1 END) AND @state:=State ORDER BY State, PersonName;此查询将在状态更改时重置排序。@row_number变量用于跟踪序列号，@state变量用于跟踪当前状态。ORDER BY子句用于按州和人名对记录进行排序。
我希望这对你有帮助！
k97glaaz2#
非常感谢@Adesegun丹尼尔;这里有一个微小的修改，最终成为一个明确的解决方案。