根据其他列的唯一性增加列值

Question

我有一个表格，需要添加一个递增列，但是增量应基于其他列中的现有值。

select * from mytable;

first_col    second_col
   A             B
   A             C
   A             D
   A             E
   A             B
   A             D

现在，我想添加另一列，比如说new_column，其值根据first_col和second_col唯一递增。

该列应该像这样填充：

first_col    second_col    new_col
   A             B            1
   A             C            1
   A             D            1
   A             E            1
   A             B            2
   A             D            2
   A             B            3

是否可以使用MySQL内置的自增策略来实现此操作。

- LearningToDesign

Answer 1

使用带有自增id列的临时表，您可以执行以下操作

create temporary table tt (
  id int auto_increment primary key,
  col1 varchar(32),col2 varchar(32));

insert into tt
select col1, col2 from origtable;

select col1, col2, 
  (select count(*)+1 from tt s
    where s.col1=m.col1 and s.col2=m.col2
    and s.id<m.id) n
frm tt m

- Carsten Massmann

---

谢谢。这似乎是更标准的方法。只是，我不会将序列生成器表设置为临时的。 - LearningToDesign

Answer 2

MySQL 中没有内置的增量方法，但您可以使用相关子查询或变量来实现：

select t.*,
       (@rn := if(@c = concat(first_col, ':', second_col), @rn + 1,
                  @c := concat(first_col, ':', second_col), 1, 1
                 )
       ) as new_col
from mytable t cross join
     (select @rn := 0, @c := '') params
order by first_col, second_col;

注意：这会重新排列结果。如果您想要按原始顺序排序的结果，则需要一个指定排序的列。

- Gordon Linoff

Answer 3

以下是如何操作，将col1val和col2val替换为要插入的值。

INSERT INTO mytable (first_col, second_col, new_col)
VALUES (SELECT col1val, col2val SUM(COUNT(*), 1)
        FROM mytable
        GROUP BY first_col, second_col
        HAVING first_col = col1val AND second_col = col2val)

请注意，这是一个插入查询，只会影响新插入的值。

- John Bupit

---

这将仅更新新列的值一次。我希望我可以持续添加新行，并使新列自动获取值。 - LearningToDesign