oracle删除重复数据

数据库没设主键，当要设主键时发现表中已有重复数据。下面的代码展示了删除重复数据的方法：

--创建测试表
-- Create table
create table TEST_T1
(
  test_id   VARCHAR2(8),
  test_name VARCHAR2(10),
  test_type VARCHAR2(4)
)
tablespace EXAMPLE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table TEST_T1
  is '测试表';

--添加数据
insert into TEST_T1 (test_id, test_name, test_type)
values ('1', 'NAME1', '1');
insert into TEST_T1 (test_id, test_name, test_type)
values ('1', 'NAME2', '2');
insert into TEST_T1 (test_id, test_name, test_type)
values ('2', 'NAME3', '1');
insert into TEST_T1 (test_id, test_name, test_type)
values ('2', 'NAME4', '2');
insert into TEST_T1 (test_id, test_name, test_type)
values ('3', 'NAME5', '1');
insert into TEST_T1 (test_id, test_name, test_type)
values ('3', 'NAME6', '2');
insert into TEST_T1 (test_id, test_name, test_type)
values ('4', 'NAME7', '1');
insert into TEST_T1 (test_id, test_name, test_type)
values ('5', 'NAME8', '2');
insert into TEST_T1 (test_id, test_name, test_type)
values ('6', 'NAME9', '1');

--查询
select t.*, t.rowid from TEST_T1 t ;
--确认主键是否重复
select t.test_id from test_t1 t
group by t.test_id
having count(*)>1
select t.test_id,t.test_type from test_t1 t
group by t.test_id,t.test_type
having count(*)>1

--选择重复数据
select t.*,rowid from TEST_T1 t 
where t.test_id in (
                    select t.test_id  from TEST_T1 t 
                    group by t.test_id
                    having count(*)>1
      )
      and rowid not in (
                    select min(rowid) from TEST_T1 t 
                    group by t.test_id
                    having count(*)>1 
      ) 
--删除重复数据，只各留一条
delete from TEST_T1 t 
where t.test_id in (
                    select t.test_id  from TEST_T1 t 
                    group by t.test_id
                    having count(*)>1
      )
      and rowid not in (
                    select min(rowid) from TEST_T1 t 
                    group by t.test_id
                    having count(*)>1 
      ) 

以上测试表假设要设的主键只有一个(test_id),如果主键为多个的情况，可以在代码中相应部分替换掉test_id。如下：

select t.*,rowid from SR_HCS4FYKM t 
where (CSKCSID,CSKPJLX,CSKKMBH) in (
                    select CSKCSID,CSKPJLX,CSKKMBH  from SR_HCS4FYKM t 
                    group by CSKCSID,CSKPJLX,CSKKMBH
                    having count(*)>1
      )
      and rowid not in (
                    select min(rowid) from SR_HCS4FYKM t 
                    group by CSKCSID,CSKPJLX,CSKKMBH
                    having count(*)>1 
      ) 

删除了重复数据后就可以设置主键(test_id)了。