CREATE TABLE tb(col1 int,col2 int);INSERT INTO tb(col1) VALUES(1); INSERT INTO tb(col1) VALUES(1); INSERT INTO tb(col1) VALUES(2); INSERT INTO tb(col1) VALUES(2); INSERT INTO tb(col1) VALUES(2); INSERT INTO tb(col1) VALUES(3); INSERT INTO tb(col1) VALUES(4); INSERT INTO tb(col1) VALUES(4);ALTER TABLE tb ADD COLUMN ID INT AUTO_INCREMENT PRIMARY KEY UPDATE tb AS A INNER JOIN tb AS B ON A.col1=B.col1 AND A.ID >= B.ID INNER JOIN tb AS C ON A.ID=C.ID SET C.col2=C.ID-B.ID+1;ALTER TABLE tb DROP COLUMN ID;SELECT * FROM tb;DROP TABLE tb;/* col1 col2 ----- ----- 1 1 1 2 2 1 2 2 2 3 3 1 4 1 4 2*/
INSERT INTO tb(col1) VALUES(1);
INSERT INTO tb(col1) VALUES(2);
INSERT INTO tb(col1) VALUES(2);
INSERT INTO tb(col1) VALUES(2);
INSERT INTO tb(col1) VALUES(3);
INSERT INTO tb(col1) VALUES(4);
INSERT INTO tb(col1) VALUES(4);ALTER TABLE tb ADD COLUMN ID INT AUTO_INCREMENT PRIMARY KEY UPDATE tb AS A INNER JOIN tb AS B
ON A.col1=B.col1 AND A.ID >= B.ID
INNER JOIN tb AS C
ON A.ID=C.ID
SET C.col2=C.ID-B.ID+1;ALTER TABLE tb DROP COLUMN ID;SELECT * FROM tb;DROP TABLE tb;/*
col1 col2
----- -----
1 1
1 2
2 1
2 2
2 3
3 1
4 1
4 2*/