oracle删除特定重复记录

Question

例如：
---------------------------------
sys_1 sys_2 sys_3 sys4
10001
10001 20001
10001 20002
10001
10001 40001
10002
20002 30001
30001
40001
40002
10003
                          40003
-------------------------------
获得结过是
---------------------------------
sys_1 sys_2 sys_3 sys4
10001 20001
10001 20002
10001 40001
10002
20002 30001
40002
10003
                          40003
----------------------------------
大概是这样的：当sys_1列同一值有两条以上记录时：数目a：sys_2，sys_3，sys4等三列不管任何一列有记录的记录数；数目b其他三列都为空的记录数(一定存在记录)， a>0时 获得的记录是满足a和满足b中的一条记录；当a=0时获得的记录是满足满足b中的一条记录。以此类推。其他列也是这样过滤数据的。

Answer 1

大概是这样的：sys_1列同一值有两条以上记录（一定存在）：数目a：sys_2，sys_3，sys4等三列不管任何一列有记录的记录数；数目b其他三列都为空的记录数(一定存在记录)， a>0时 获得的记录是满足a和满足b中的一条记录；当a=0时获得的记录是满足满足b中的一条记录。以此类推。其他列也是这样过滤数据的。

Answer 2

由于显示的关系，
sys_1 的记录是10001 
sys_2 的记录是 20001
sys_3 的记录是 30001
sys4  的记录是 40001

Answer 3

SQL> SELECT * FROM tb;SYS_1 SYS_2 SYS_3 SYS_4                                                         
----- ----- ----- -----                                                         
10001                                                                           
10001 20001                                                                     
10001 20002                                                                     
10001                                                                           
10001             40001                                                         
10002                                                                           
      20002 30001                                                               
            30001                                                               
                  40001                                                         
                  40002                                                         
10003                                                                         
                  40003                                                         已选择12行。SQL> DELETE FROM tb WHERE ROWID NOT IN (SELECT ROWID FROM tb WHERE sys_1 IN (SELECT sys_1 FROM tb GROUP BY sys_1 HAVING COUNT(1) = 1) OR sys_2 IS NOT NULL OR sys_3 IS NOT NULL OR sys_4 IS NOT NULL);已删除2行。SQL> DELETE FROM tb WHERE ROWID NOT IN (SELECT ROWID FROM tb WHERE sys_2 IN (SELECT sys_2 FROM tb GROUP BY sys_2 HAVING COUNT(1) = 1) OR sys_1 IS NOT NULL OR sys_3 IS NOT NULL OR sys_4 IS NOT NULL);已删除0行。SQL> DELETE FROM tb WHERE ROWID NOT IN (SELECT ROWID FROM tb WHERE sys_3 IN (SELECT sys_3 FROM tb GROUP BY sys_3 HAVING COUNT(1) = 1) OR sys_1 IS NOT NULL OR sys_2 IS NOT NULL OR sys_4 IS NOT NULL);已删除 1 行。SQL> DELETE FROM tb WHERE ROWID NOT IN (SELECT ROWID FROM tb WHERE sys_4 IN (SELECT sys_4 FROM tb GROUP BY sys_4 HAVING COUNT(1) = 1) OR sys_1 IS NOT NULL OR sys_2 IS NOT NULL OR sys_3 IS NOT NULL);已删除 1 行。SQL> SELECT * FROM tb;SYS_1 SYS_2 SYS_3 SYS_4                                                         
----- ----- ----- -----                                                         
10001 20001                                                                     
10001 20002                                                                     
10001             40001                                                         
10002                                                                           
      20002 30001                                                               
                  40002                                                         
10003                                                                           
                  40003                                                         已选择8行。

Answer 4

未处理数据矩阵
   --------------------------------------------
SYS_0      SYS_1         SYS_2         SYS_3         SYS_4
50001      10001
50003      10001         20001
50004      10001         20002
50005      10001   
50006      10001                                     40001
50007      10002 
50008                    20002         30001
50009                                  30001
50010                                                40001
50011                                                40002
 
处理后数据矩阵
--------------------------------------------------------------
SYS_0      SYS_1         SYS_2         SYS_3         SYS_4 
50003      10001         20001
50004      10001         20002  
50006      10001                                     40001
50008                    20002         30001
50011                                                40002
 
满足条件1: 对于相同记录的SYS_1(后四列中任意一个)来说 SYS_2,SYS_3,SYS_4等任一列存在值，则保留。
如果满足条件1存在记录，则把SYS_2,SYS_3,SYS_4都为空的记录全部删除，否则保留最大/最小的SYS_0的记录

Answer 5

-----你的思路好清晰哈我不假思索的写出来了。。呵呵
SQL> with tab as (
  2  select 50001 SYS_0,10001 SYS_1,null  SYS_2,null SYS_3, null SYS_4 from dual union all
  3  select 50003 SYS_0,10001 SYS_1,20001 SYS_2,null SYS_3, null SYS_4 from dual union all
  4  select 50004 SYS_0,10001 SYS_1,20002 SYS_2,null SYS_3, null SYS_4 from dual union all
  5  select 50005 SYS_0,10001 SYS_1,null  SYS_2,null SYS_3, null SYS_4 from dual union all
  6  select 50006 SYS_0,10001 SYS_1,null  SYS_2,null SYS_3, 40001 SYS_4 from dual union all
  7  select 50007 SYS_0,10002 SYS_1,null  SYS_2,null SYS_3, null SYS_4 from dual union all
  8  select 50008 SYS_0,null SYS_1,20002  SYS_2,30001 SYS_3, null SYS_4 from dual union all
  9  select 50009 SYS_0,null SYS_1, null  SYS_2, 30001 SYS_3, null SYS_4 from dual union all
 10  select 50010 SYS_0,null SYS_1, null  SYS_2,null SYS_3, 40001 SYS_4 from dual union all
 11  select 50011 SYS_0,null SYS_1, null  SYS_2,null SYS_3,  40002 SYS_4 from dual
 12  )
 13   select *
 14     from tab
 15    where sys_1 is not null
 16      and (SYS_2 is not null or SYS_3 is not null or SYS_4 is not null)
 17   union all
 18   select *
 19     from tab
 20    where sys_1 is null
 21      and SYS_0 = (select min(SYS_0) from tab where sys_1 is null)
 22   union all
 23   select *
 24     from tab
 25    where sys_1 is null
 26      and SYS_0 = (select max(SYS_0) from tab where sys_1 is null)
 27  ;     SYS_0      SYS_1      SYS_2      SYS_3      SYS_4
---------- ---------- ---------- ---------- ----------
     50003      10001      20001            
     50004      10001      20002            
     50006      10001                            40001
     50008                 20002      30001 
     50011                                       40002SQL> 

Answer 6

with t_test as ( 
select 5001 sys0,10001 sys1,null sys2,null sys3,null sys4 from dual union all 
select 5002 sys0,10001,         20001,          null, null    from dual union all 
select 5003 sys0,10001,         20002,          null, null    from dual union all 
select 5004 sys0,10001,          null,          null, null    from dual union all 
select 5005 sys0,10001,          null,          null, 40001   from dual union all     
select 5006 sys0,10002,          null,          null, null    from dual union all 
select 5007 sys0,null,          20002,         30001, null    from dual union all 
select 5008 sys0,null,           null,         30001, null    from dual union all 
select 5009 sys0,null,           null,          null, 40001   from dual union all       
select 5010 sys0,null,           null,          null, 40002   from dual
   )
select sys0,sys1,sys2,sys3,sys4 
 from (select sys0,sys1,sys2,sys3,sys4,
              nvl(sys2,0)+nvl(sys3,0)+nvl(sys4,0) sys5, -- 每一行2,3,4是否同时为空
              sum(nvl(sys2,0)+nvl(sys3,0)+nvl(sys4,0)) over(partition by sys1) sys6 , --是否所有行的2,3,4都为空
              max(sys0) over(partition by sys1) maxsys0, 
              min(sys0) over(partition by sys1) minsys0  
              --以此类推可以处理sys2,sys3,sys4的情况
              
         from t_test
      )a
where a.sys5 > 0  or (sys6 = 0 and sys0 in (maxsys0,minsys0))  目前仅处理sys1的情况，sys2,sys3,sys4可以同样的方法处理，
这种方法能够减少表扫描的次数，但会增加cpu的运算次数

Answer 7

你的sys_2 sys_3 sys4 中字符长度固定吗？固定的话好办，用连接起来的长度来判断。

Answer 8

To  Oraclefans_
感觉你对处理方式有局限性，我的数据上千万级，每天最少也有几十万。等待最好性能的方式

Answer 9

-----这样呢？
SQL> with tab as (
  2   select 50001 SYS_0,10001 SYS_1,null  SYS_2,null SYS_3, null SYS_4 from dual union all
  3    select 50003 SYS_0,10001 SYS_1,20001 SYS_2,null SYS_3, null SYS_4 from dual union all
  4   select 50004 SYS_0,10001 SYS_1,20002 SYS_2,null SYS_3, null SYS_4 from dual union all
  5     select 50005 SYS_0,10001 SYS_1,null  SYS_2,null SYS_3, null SYS_4 from dual union all
  6   select 50006 SYS_0,10001 SYS_1,null  SYS_2,null SYS_3, 40001 SYS_4 from dual union all
  7    select 50007 SYS_0,10002 SYS_1,null  SYS_2,null SYS_3, null SYS_4 from dual union all
  8    select 50008 SYS_0,null SYS_1,20002  SYS_2,30001 SYS_3, null SYS_4 from dual union all
  9    select 50009 SYS_0,null SYS_1, null  SYS_2, 30001 SYS_3, null SYS_4 from dual union all
 10   select 50010 SYS_0,null SYS_1, null  SYS_2,null SYS_3, 40001 SYS_4 from dual union all
 11    select 50011 SYS_0,null SYS_1, null  SYS_2,null SYS_3,  40002 SYS_4 from dual
 12   )
 13  select *
 14    from tab
 15   where sys_1 is not null
 16     and (SYS_2 is not null or SYS_3 is not null or SYS_4 is not null)
 17  union all
 18  select *
 19    from tab a
 20   where a.sys_1 is null
 21     and (not exists (select 1
 22                        from tab b
 23                       where b.sys_1 is null
 24                         and a.sys_0 > b.sys_0) or not exists
 25          (select 1
 26             from tab b
 27            where b.sys_1 is null
 28              and a.sys_0 < b.sys_0))
 29  ;     SYS_0      SYS_1      SYS_2      SYS_3      SYS_4
---------- ---------- ---------- ---------- ----------
     50003      10001      20001            
     50004      10001      20002            
     50006      10001                            40001
     50008                 20002      30001 
     50011                                       40002SQL>