DML 中的自动增量逻辑

Question

我有两个表test2和test_hist.我想将数据从test2加载到test_hist中，但由于唯一约束而失败。

CREATE TABLE TEST2 (ID NUMBER , TEXT VARCHAR2(10));
create table test_hist (id number , text varchar2(10) , constraint t_pk PRIMARY key (id , text));
INSERT INTO TEST2 VALUES(100 , '20180909-I');
INSERT INTO TEST2 VALUES(101 , '20180909-I');
INSERT INTO TEST2 VALUES(102 , '20180809-I');
INSERT INTO TEST2 VALUES(100 , '20180909-I');
COMMIT;
INSERT INTO test_hist SELECT ID , TEXT FROM TEST2;

我想在有重复项时附加带有自动增量编号的 TEXT 字段，如下所示。

expected OUTPUT 
ID         TEXT
100      20180909-I
101      20180909-I
102      20180809-I
100      20180909-I-1
100      20180909-I-2
102      20180809-I-1

谁能帮助我实现这一目标。 提前致谢

如果我多次执行插入语句，它应该插入到带有自动增量文本的test_hist中。 例如

insert into test_hist
select id,
text || case when row_number() over (partition by id, text order by null) > 1
then (1 - row_number() over (partition by id, text order by null)) end
from test2;
9 rows inserted.
select *
from test_hist
order by id, text;
ID TEXT        
---------- ------------
100 20180909-I  
100 20180909-I-1
100 20180909-I-2
100 20180909-I-3
101 20180909-I  
101 20180909-I-1
102 20180809-I  
102 20180809-I-1
102 20180809-I-2 

Answer 1

与@Barbaros相同的基本思想，但排列方式略有不同，并且第二个表的列大小增加，以便它可以保存修改后的值：

create table test2 (
id number, text varchar2(10)
);
create table test_hist (id number,
text varchar2(12), -- increased size to 12; may need to be larger
constraint t_pk primary key (id , text)
);
insert into test2 values(100 , '20180909-I');
insert into test2 values(101 , '20180909-I');
insert into test2 values(102 , '20180809-I');
insert into test2 values(100 , '20180909-I');
insert into test2 values(100 , '20180909-I');
insert into test2 values(102 , '20180809-I');

然后是相同的分析函数，如果您不介意重复它，请在一个级别中包括所有 PK 列：

insert into test_hist
select id,
text || case when row_number() over (partition by id, text order by null) > 1
then (1 - row_number() over (partition by id, text order by null)) end
from test2;
6 rows inserted.
select *
from test_hist
order by id, text;
ID TEXT        
---------- ------------
100 20180909-I  
100 20180909-I-1
100 20180909-I-2
101 20180909-I  
102 20180809-I  
102 20180809-I-1

如果你在真实场景中实际上有更多的列，并且原始表中还有另一个非PK列，你想影响历史行的递增顺序，你可以在函数的order by中使用它而不是null，这实际上只是一个虚拟占位符。

---

如果它需要在多个插入中继续执行相同的操作(这表明数据模型问题甚至比原始要求还要严重(，那么您也需要计算历史记录表中的现有匹配项。

从与以前相同的原始数据和空历史记录表开始：

insert into test_hist
select id,
text || case when appearance > 1 then (1 - appearance) end
from (
select t.id,
t.text,
row_number() over (partition by t.id, t.text order by null) + (
select count(*) from test_hist th
where th.id = t.id
and th.text like t.text || '%'
) as appearance
from test2 t
);
6 rows inserted.
select *
from test_hist
order by id, text;
ID TEXT        
---------- ------------
100 20180909-I  
100 20180909-I-1
100 20180909-I-2
101 20180909-I  
102 20180809-I  
102 20180809-I-1
6 rows selected. 

并再次运行相同的语句：

insert into test_hist
select id,
text || case when appearance > 1 then (1 - appearance) end
from (
select t.id,
t.text,
row_number() over (partition by t.id, t.text order by null) + (
select count(*) from test_hist th
where th.id = t.id
and th.text like t.text || '%'
) as appearance
from test2 t
);
6 rows inserted.
select *
from test_hist
order by id, text;
ID TEXT        
---------- ------------
100 20180909-I  
100 20180909-I-1
100 20180909-I-2
100 20180909-I-3
100 20180909-I-4
100 20180909-I-5
101 20180909-I  
101 20180909-I-1
102 20180809-I  
102 20180809-I-1
102 20180809-I-2
102 20180809-I-3
12 rows selected. 

可能有一些方法可以优化它，这样您就不必经常点击历史记录表，但这可能会为您提供一个起点。

使用like实际上意味着这仅在文本始终相同长度时才有效，或者至少你不能有作为其他值扩展的值;否则你会得到比你想要的更多的匹配。至少从您的示例数据来看，这看起来不是问题。如有必要，您可以通过切换到regexp_like来解决此问题，具体取决于您的实际数据。

Answer 2

您可以尝试以下操作：

select ID, text||decode(rn-1,0,null,'-'||(rn-1)) as text
from
(
with test2(rnk,ID,text) as
(
select 1, 100 , '20180909-I' from dual union all
select 2, 101 , '20180909-I' from dual union all
select 3, 102 , '20180809-I' from dual union all
select 4, 100 , '20180909-I' from dual union all
select 5, 100 , '20180909-I' from dual union all
select 6, 102 , '20180909-I' from dual
)
select t.ID, t.rnk, 
t.text, row_number() over (partition by ID order by Text,ID) as rn
from test2 t
)
order by rn, rnk

Rextester 演示