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屋!