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

如果你想的话；

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

Answer 3

非常难看，但可以在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