表中的更新顺序-Oracle [英] Update sequence in table - Oracle
本文介绍了表中的更新顺序-Oracle的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一张桌子:
table1
u_a_id element_id my_seq line_num
1 HI01-01 1 30
1 HI01-02 1 30
1 HI01-01 1 31
1 HI01-02 1 31
1 HI02-03 1 31
1 HI02-04 1 31
2 HI01-01 1 40
2 HI01-02 1 40
2 HI02-01 1 40
2 HI02-02 1 40
2 HI02-03 1 40
2 HI02-04 1 40
2 HI03-02 1 41
2 HI03-03 1 41
2 HI05-04 1 41
2 HI05-05 1 41
如果新的HI01出现在相同的u_a_id中，或者需要更改HI的计数器(例如)，我需要更新my_seq. HI01-> HI02，按line_num为每个u_a_id订单.
I need to update my_seq if a new HI01 appears in the same u_a_id or the counter for HI changes, for ex. HI01 -> HI02 for each u_a_id order by line_num.
我有这个查询，但是即使对于u_a_id = 1的HI01-01的第二个实例，它的seq也为1.
I have this query, however this gives seq as 1 even for second instance of HI01-01 in u_a_id = 1:
select t.*,
dense_rank() over (partition by u_a_id order by substr(element_id, 1, 4)) as new_my_seq
from table1 t
输出如下:
u_a_id element_id my_seq line_num
1 HI01-01 1 30
1 HI01-02 1 30
1 HI01-01 2 31
1 HI01-02 2 31
1 HI02-03 3 31
1 HI02-04 3 31
2 HI01-01 1 40
2 HI01-02 1 40
2 HI02-01 2 40
2 HI02-02 2 40
2 HI02-03 2 40
2 HI02-04 2 40
2 HI03-02 3 41
2 HI03-03 3 41
2 HI05-04 4 41
2 HI05-05 4 41
Oracle SQL中是否有一种方法可以实现这一目标?
Is there a way in Oracle SQL to achieve this?
推荐答案
我认为您只想要:
select t.*,
dense_rank() over (partition by u_a_id
order by line_num,
substr(element_id, 1, 4)
) as my_seq
from t;
这篇关于表中的更新顺序-Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
