Oracle SQL -基于列增加序列

Question

我有一个表:

ITEM_EXPENSES在列:

ITEM | SUPPLIER | SEQ_NO | COUNTRY
100      A10        1       UAE
100      A10        2       SAU

我有另一个具有相同结构的表ITEM_EXPENSES_DELTA

ITEM | SUPPLIER | SEQ_NO | VALUE
100      A10        1       USA
100      A10        2       THA

我想在ITEM_EXPENSES中插入ITEM_EXPENSES_DELTA，但是唯一键是ITEM, SUPPLIER, SEQ_NO

我想要的数据是这样的:

ITEM | SUPPLIER | SEQ_NO | VALUE
100      A10        1       UAE
100      A10        2       SAU
100      A10        3       USA
100      A10        4       THA

seq_no应该是ITEM_EXPENSES

中已经存在的下一个数字应该插入什么

Answer 1

这里有一个选项:

样本数据:

SQL> select * from item_expenses;
      ITEM SUP     SEQ_NO COU
---------- --- ---------- ---
       100 A10          1 UAE
       100 A10          2 SAU
SQL> select * from item_expenses_delta;
      ITEM SUP     SEQ_NO COU
---------- --- ---------- ---
       100 A10          1 USA
       100 A10          2 THA

插入:

SQL> insert into item_expenses (item, supplier, seq_no, country)
  2  with ms as
  3    (select max(seq_no) maxseq from item_expenses)
  4  select d.item, d.supplier, s.maxseq + rownum, country
  5  from item_expenses_delta d cross join ms s;
2 rows created.

结果:

SQL> select * from item_expenses;
      ITEM SUP     SEQ_NO COU
---------- --- ---------- ---
       100 A10          1 UAE
       100 A10          2 SAU
       100 A10          3 USA
       100 A10          4 THA
SQL>