mysql中重复记录的编号(2)

Question

我有这张桌子，(sequence_No.)字段是null：

ID        Name      age     sequence_No.
--        -----     ---     ------------
1         sara      20         
2         sara      20           
3         sara      20         
4         john      24         
5         john      24         
6         Hama      23         

我想将其更新为：

ID        Name      age     sequence_No.
--        -----     ---     ------------
1         sara      20         1
2         sara      20         2
3         sara      20         3
4         john      24         1
5         john      24         2
6         Hama      23         1

在mysql中哪个查询可以做到这一点？

谢谢

Answer 1

您可以在mysql中使用关联子查询来模拟ROW_NUMBER()。带有序列号的结果表将与表本身连接，并使用生成的数字更新sequence_No的值。

UPDATE  tableName a
        INNER JOIN
        (
            SELECT  A.ID, 
                    (
                        SELECT  COUNT(*)
                        FROM    tableName c
                        WHERE   c.Name = a.Name AND
                                c.ID <= a.ID) AS sequence_No
            FROM    TableName a
        ) b ON a.ID = b.ID
SET     a.sequence_No = b.sequence_No

• SQLFiddle演示

Answer 2

SELECT  ID, Name, age, sequence_No
FROM
        (
            select  ID,
                    Name,
                    age,
                    @sum := if(@nme = Name AND @acct = age, @sum ,0) + 1 sequence_No,
                    @nme := Name,
                    @acct := age
            from    TableName,
                    (select @nme := '', @sum := 0, @acct := '') vars
            order   by  Name, age
        ) s
ORDER   BY ID

或者你可以用

SELECT 
ID,
Name,
age,
( 
CASE Name 
WHEN @curType 
THEN @curRow := @curRow + 1 
ELSE @curRow := 1 AND @curType := Name END
) + 1 AS sequence_No

FROM student, (SELECT @curRow := 0, @curType := '') r
ORDER BY  ID,NAME;

虽然我还没有进行测试，但是可以使用相同的方法来更新表。