oracle删除重复数据
数据库没设主键，当要设主键时发现表中已有重复数据。下面的代码展示了删除重复数据的方法：
--创建测试表
-- Create table
create table TEST_T1
(
test_id VARCHAR2(8),
test_name VARCHAR2(10),
test_type VARCHAR2(4)
) www.2cto.com
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'); www.2cto.com
--查询
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 www.2cto.com
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。
删除了重复数据后就可以设置主键(test_id)了。
作者 qinxike