MySQL:根据另一个字段添加序列列 [英] MySQL: Add sequence column based on another field

问题描述

我正在处理一些旧代码/数据库，需要在数据库中添加一个字段，该字段将记录与该(外部)ID相关的序列号.

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           ...

我需要添加一个sequenceid列，该列针对每个帐户分别递增，以实现:

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.

有什么方法可以用SQL来实现这一目标吗?还是我依靠PHP脚本来为我完成这项工作?

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)

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;

我称该表为"test"；显然，需要正确设置.您必须使用一个临时表，因为MySQL不允许您使用要更新的同一表中的子选择.

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.

这篇关于MySQL:根据另一个字段添加序列列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！