mysql - mysql中重复记录的编号

Question

我有 Student 表，我想像这样更新 (sequence_No.) 字段:

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

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;

SQL FIDDLE