将键添加到SQl数据库中的唯一值

My SQL data looks like this:

我的SQL数据如下所示：

Col1
A
A
A
B
B
C
D

I want to add a key to only unique values. So the end result will look like this:

我想只为唯一值添加一个键。所以最终结果将如下所示：

Col1  Col2
A       1
A       1
A       1
B       2
B       2
C       3
D       3

How can I do this?

我怎样才能做到这一点？

1 个解决方案

#1

You can do this with the dense_rank() window function:

您可以使用dense_rank（）窗口函数执行此操作：

select col1, dense_rank() over (order by col1) as col2
from t;

This solves the problem as a query. If you want to actually change the table, then the code is more like:

这解决了作为查询的问题。如果你想实际更改表，那么代码更像是：

alter table t add col2 int;

with toupdate as (
    select t.*, dense_rank() over (order by col1) as newcol2
    from t
)
update toupdate
    set col2 = newcol2;