我有一张这样的table
+-----+-----+-----+-----+
|att1 | att2| att3| id |
+-----------------------+
| a | b | a | 1 |
| b | c | c | 2 |
| a | b | a | 3 |
+-----------------------+
如果att1、att2和att3相等,我想用一个相等的新id替换该id,如下所示:
+-----+-----+-----+-----+
|att1 | att2| att3| id |
+-----------------------+
| a | b | a | 1 |
| b | c | c | 2 |
| a | b | a | 1 |
+-----------------------+
我尝试了经典的merge-into语句,在属性上使用密集的\u rank()函数,如下所示:
merge into the_table t
using (
select att1, att2, att3, dense_rank() over (partition by att1, att2, att3 order by att1) as rnk
from the_table
) x on (x.att1 = t.att1 and x.att2 = t.att2 and t.att3 = x.att3)
when matched then update
set old_id = x.new_id;
但这行不通,因为 select att1, att2, att3, dense_rank() over (partition by att1, att2, att3 order by att1) as rnk
并没有像我预期的那样给att1、att2和att3的每个分区分配自己的等级,而是所有的分区都得到了等级1。
我做错什么了?
2条答案
按热度按时间goucqfw61#
这只是另一种选择-
此处演示
wbrvyc0a2#
回答我自己的问题:
带有rank函数的select应该如下所示,然后按预期工作
因此,将应该获得相同秩的属性放入order by而不是partition by