将重复的记录移动到另一个表

I have a table MAIN which has duplicates on ID field as below:

我有一个表主，ID字段上有重复如下:

ID      SYSTEM  FLAG  FIRST_NAME   LAST_NAME       TERMDATE
A021    Alpha   Y     JOHN         DOE             null
A021    Beta    N     JOHN         DOE             05-Jun-17
C045    Beta    Y     PETER        PARKER          null
C045    Omega   N     PETER        PARKER          05-Jan-17
D078    Alpha   N     TONY         STARK           07-Dec-17
D078    Gamma   Y     TONY         STARK           null
X039    Gamma   Y     STEVE        ROGERS          null
X039    Gamma   Y     STEVE        ROGERS          null

As you can see I have duplicates in ID field. I want to keep the records with null data in MAIN table and move others to a duplicate table. So I want my MAIN table's output to look like:

正如你看到的，我在ID字段中有重复。我希望在主表中保留记录的空数据，并将其他数据移到另一个表中。所以我想让主表的输出看起来像这样:

ID      SYSTEM  FLAG  FIRST_NAME   LAST_NAME       TERMDATE
A021    Alpha   Y     JOHN         DOE             null
C045    Beta    Y     PETER        PARKER          null
D078    Gamma   Y     TONY         STARK           null
X039    Gamma   Y     STEVE        ROGERS          null

And the duplicates should be moved to a DUPLICATE TABLE which should look like:

并将复制表移至复制表中，复制表应如下:

ID      SYSTEM  FLAG    FIRST_NAME  LAST_NAME      TERMDATE
A021    Beta    N     JOHN          DOE            05-Jun-17
C045    Omega   N     PETER         PARKER         05-Jan-17
D078    Alpha   N     TONY          STARK          07-Dec-17

To be noted the records that are exact duplicates were not moved to duplicate table e.x. X039 would be deleted entirely and not moved to duplicate table.

需要注意的是，没有将完全重复的记录移动到重复的表e.x。X039将被完全删除，而不会移动到重复的表。

I can't come to a script that will achieve this result.

我无法找到一个能达到这个结果的脚本。

2 个解决方案

#1

Yet another option:

另一个选择:

Table contents:

表内容:

SQL> select * From main order by id;

ID    SYSTEM     F FIRST_NAME TERMDATE
----- ---------- - ---------- ----------
a021  alpha      y john
a021  beta       n john       05.06.2017
c045  beta       y peter
c045  omega      n peter      05.01.2017
d078  alpha      n tony       07.12.2017
d078  gamma      y tony
x039  gamma      y steve
x039  gamma      y steve

8 rows selected.

Duplicates: ranked by TERMDATE within the same ID. Insert them into DUPLICATES & remove them from MAIN. You can't just "move" them (take from here and put there; you'll have to do it in 2 steps):

重复:按同一ID内的TERMDATE排序。将它们插入到重复的内容中并从MAIN中删除。你不能只是“移动”它们(从这里开始，然后放到那里;你得分两步来做):

SQL> insert into duplicate
  2  select id, system, flag, first_name, termdate
  3  from  (select id, system, flag, first_name, termdate,
  4           rank() over (partition by id order by termdate nulls first) rn
  5         from main
  6        )
  7  where rn > 1;

3 rows created.

SQL> delete from main
  2  where (       id, system, flag, first_name, termdate) in
  3        (select id, system, flag, first_name, termdate
  4         from duplicate
  5        );

3 rows deleted.

Finally, delete remaining duplicates from MAIN:

最后，从MAIN中删除剩余的副本:

SQL> delete from main m
  2  where exists (select null from main m1
  3                where m1.id = m.id
  4                  and m1.rowid > m.rowid
  5               );

1 row deleted.

The result:

结果:

SQL> select * From main order by id;

ID    SYSTEM     F FIRST_NAME TERMDATE
----- ---------- - ---------- ----------
a021  alpha      y john
c045  beta       y peter
d078  gamma      y tony
x039  gamma      y steve

SQL>
SQL> select * from duplicate order by id;

ID    SYSTEM     F FIRST_NAME TERMDATE
----- ---------- - ---------- ----------
a021  beta       n john       05.06.2017
c045  omega      n peter      05.01.2017
d078  alpha      n tony       07.12.2017

SQL>

#2

Your basic question is handled by:

你的基本问题是:

insert into duplicates (ID, SYSTEM, FLAG, FIRST_NAME, LAST_NAME, TERMDATE)
    select ID, SYSTEM, FLAG, FIRST_NAME, LAST_NAME, TERMDATE
    from main
    where termdate is not null;

delete from duplicates
    where termdate is not null;

delete from duplicates
    where rowid not in (select min(m2.rowid)
                        from main m2
                        group by ID, SYSTEM, FLAG, FIRST_NAME, LAST_NAME, TERMDATE
                       );

If anything might be happening on the database, you may want to wrap all this in a transaction.

如果数据库上发生了什么事情，您可能希望将所有这些都打包到事务中。

#1