多种去重方式比较

--去重-以其它条件去除重复信息---建议首先使用row——number来实现，因为无论是在数据库兼容，多种限制条件去重，sql逻辑复杂度，去重返回效率都是最好的

--最快的
delete from dfg where rowid in
(select r from
(select rowid r, row_number() over(partition by dt,name order by sal desc) m from dfg)
where m!=1)

--其次
delete
from 表名
where rowid not in
 (select rowid
  from (select rowid,max(rowid) over(partition by 分组元素) n--筛选重复唯一值
        from (select 分组元素,筛选元素,max(筛选元素) over(partition by 分组元素) m--筛选出重复最大值
              from 表名)
        where 筛选元素 = m)
  where rowid = n);
 
 --耗费比较多多
  delete
from dfg
where rowid  in
   (select rowid
        from (select sal,max(sal) over(partition by dt,name) m--筛选出重复最大值
              from dfg)
        where sal != m
    union
    select rowid
        from (select rowid,max(rowid) over(partition by dt,name,sal) n--筛选出重复最大值
              from dfg)
        where rowid != n)
 
 --单纯去重
 delete
from 表名
where rowid in
 (select rowid
  from (select 分组元素,rowid,max(rowid) over(partition by 分组元素) n--去重
        from 表名)
  where rowid ！= n);
 
 
 
  delete from 表名 where rowid in
(select r from
(select rowid r, row_number() over(partition by 分组元素 order by rowid desc) m from 表名)
where m!=1)

--建表实验
create table dfg(dt date,name varchar(30),sal number(5));
insert all into dfg values(date'2013-2-3','lji',1111)
           into dfg values(date'2013-2-3','lji',0)
           into dfg values(date'2013-2-3','lji',1111)
           into dfg values(date'2013-2-3','ljie',2222)
           into dfg values(date'2013-2-3','ljie',222)
           into dfg values(date'2013-2-3','ljie',0)
           into dfg values(date'2013-2-4','lji',346)
           into dfg values(date'2013-2-4','lji',10)
           into dfg values(date'2013-2-4','lji',3456)
           into dfg values(date'2013-2-4','ljie',4444)
           into dfg values(date'2013-2-4','ljie',4444)
           into dfg values(date'2013-2-4','ljie',4444)
           into dfg values(date'2013-2-4','ljil',3333)
           select 1 from dual;

select * from dfg;

truncate table dfg;