mysql中加一列序列,MySQL：根据另一个字段添加序列列

I'm working on some legacy code/database, and need to add a field to the database which will record a sequence number related to that (foreign) id.

Example table data (current):

ID ACCOUNT some_other_stuff

1 1 ...

2 1 ...

3 1 ...

4 2 ...

5 2 ...

6 1 ...

I need to add a sequenceid column which increments separately for each account, achieving:

ID ACCOUNT SEQ some_other_stuff

1 1 1 ...

2 1 2 ...

3 1 3 ...

4 2 1 ...

5 2 2 ...

6 1 4 ...

Note that the sequence is related to account.

Is there a way I can achieve this in SQL, or do I resort to a PHP script to do the job for me?

TIA,

Kev

解决方案

This should work but is probably slow:

CREATE temporary table seq ( id int, seq int);

INSERT INTO seq ( id, seq )

SELECT id,

(SELECT count(*) + 1 FROM test c

WHERE c.id < test.id AND c.account = test.account) as seq

FROM test;

UPDATE test INNER join seq ON test.id = seq.id SET test.seq = seq.seq;

I have called the table 'test'; obviously that needs to be set correctly. You have to use a temporary table because MySQL will not let you use a subselect from the same table you are updating.