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

I have a table, to which I need to add an increment column, however the increment should happen based on the existing values in the other columns.

我有一个表,我需要添加一个增量列,但增量应该基于其他列中的现有值。

select * from mytable;

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

Now, I want to add another column, say new_column whose value increments uniquely on the basis of the first_col and second_col.

现在,我想添加另一列,比如new_column,其值在first_col和second_col的基础上唯一增加。

The column should be populated like these :

该列应填充如下:

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

Is it possible to do this using some sort of an MySQL in built auto increment strategy.

是否可以在内置的自动增量策略中使用某种MySQL来实现这一点。

3 个解决方案

#1

Using a temporary table with an auto_incremented id column you could do

使用具有auto_incremented 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

#2

There is no built in increment method in MySQL, but you can do this with either correlated subqueries or variables:

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;

Note: this re-orders the results. If you want the results in the original order, then you need a column that specifies that ordering.

注意:这会重新排序结果。如果您希望结果按原始顺序排列,那么您需要一个指定排序的列。

#3

Here's how you can do it, replace col1val and col2val with the values to be inserted.

以下是如何执行此操作,将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)

Note that this is an insert query, and will affect only newly inserted values.

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

#1