去重表数据（oracle/DB2数据库）

首先建立测试表，并插入一些重复数据

--drop table testt;

create table testt(
   t_id integer,
   t_name varchar2(20),
   t_age integer
);
insert into testt values(1,'a',22);
insert into testt values(1,'a',22);
insert into testt values(2,'b',23);
insert into testt values(3,'c',21);
insert into testt values(3,'c',21);
select * from testt;

-----------Oracle实现步骤-----------
--1.创建临时表，存入去重后的数据
create table temp as (select distinct * from testt);
select * from temp;
--2.删除主表
delete from testt;
--3.将临时表中的数据插入到主表中
insert into testt (select * from temp);
--4.删除临时表
drop table temp;

--查询结果
select * from testt;


---------DB2实现步骤----------
--1.建立与主表相同结构的临时表，将所有重复的数据存入临时表
create table temp2 like testt;
--或者
create table temp2 as (select * from testt where 1=2);
insert into temp2 select * from testt where t_id in
                     (select distinct t_id from testt group by t_id having count(1)>1);
--2.主表中删除存在重复的数据
delete from testt where t_id in (select distinct t_id from testt group by t_id having count(1)>1);
--3.主表中插入临时表中的数据
insert into testt select t_id,max(t_name),max(t_age) from temp2 group by t_id;
--4.删除临时表
drop table temp2;
--结果
select * from testt;