Oracle:为重复行创建序列号 [英] Oracle: create sequence number for repeated rows

问题描述

我有一个包含 3 列的表格:

I have a table with 3 columns:

ID  VALUE   SEQ

1   100     

2   100     

3   200     

4   200     

5   200 

每当VALUE"列中的值在更多行中重复时，SEQ"列中的值必须加 1.预期输出为:

Whenever value in column "VALUE is repeated in further rows, value in column "SEQ" must be incremented by 1. Expected output is:

ID  VALUE   SEQ

1   100     1

2   100     2

3   200     1

4   200     2

5   200     3

根据我的理解，在将数据归档到此表中时无法在插入查询中完成.它可能需要使用后处理来完成.我想这可以通过使用row_number() 和 partition by"的分析函数来完成，但不知道该怎么做.请让我知道，如果可以使用更好的方法来完成.任何提示将不胜感激.谢谢.

As per my understanding it can not be done in insert query while filing data in to this table. It has to be done using post-processing may be. I guess it can be done using analytical function using "row_number() and partition by", but not sure how to do it. Please let me know, if it can be done using some better way. Any hint will be appreciated. Thanks.

推荐答案

select id, value, row_number() over (partition by value order by id) as seq
from your_table;

这篇关于Oracle:为重复行创建序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！