SQL：更新一个字段，循环使用另一个字段的唯一值

Question

我有一张桌子，有两个文件夹。我想使用 ID_TASS 的每个唯一值的计数器更新 ID_ELEM。 这应该是正确的输出表：

ID_TASS, ID_ELEM   
1      , POL_1   
1      , POL_2   
1      , POL_3   
2      , POL_1   
2      , POL_2   
3      , POL_1   
4      , POL_1   
4      , POL_2   
4      , POL_3   
4      , POL_4

请，欢迎任何帮助！谢谢

Answer 1

我完全确定它是否会起作用：

UPDATE tbl
SET ID_ELEM = rn
FROM
  ( SELECT ctid AS id
         , ROW_NUMBER() OVER (PARTITION BY ID_TASS) AS rn
    FROM tbl
  ) AS tmp
WHERE tbl.ctid = tmp.ctid

或者也许是这样：

UPDATE tbl
SET ID_ELEM = ROW_NUMBER() OVER (PARTITION BY ID_TASS) 

Answer 2

非常丑陋，但可以在 mysql 上运行

SET @rank:=0;
update t1, (select id_tass, count(*) as c 
from t1
group by id_tass) T
set 
    t1.t1.id_elem = case  
    when @rank >= T.c then
    @rank:=0
    else  @rank
    end,
t1.id_elem = @rank:=@rank+1
where t1.id_tass = T.id_tass

Answer 3

如果你想;

ID_TASS ID_ELEM
1       3
1       3
2       2
2       2
3       1
4       4
4       4
4       4
4       4
1       3

你可以（t-sql）；

update tbl
    set ID_ELEM = (select COUNT(ID_TASS) from tbl where tbl.ID_TASS = T.ID_TASS)
from tbl T