有些表在设计时没有加约束条件,虽然有序列号,但是内容有很多重复的数据,比如
id 姓 名
1. 朱 亮
2. 朱 亮
3. 王 小丫
4. 张 三
declare
p_exists number:=0;
begin
select count(*) into p_exists from user_tables where table_name like upper('%t1%');
if p_exists = 1 then
execute immediate 'drop table t1';
end if;
execute immediate 'create table t1(id number NOT NULL, lastName varchar(20), firstName varchar(30))';
insert into t1 values(1,'朱','亮');
insert into t1 values(2,'朱','亮');
insert into t1 values(3,'朱','亮');
insert into t1 values(4,'王','小丫');
insert into t1 values(5,'张','三');
end;
/
select * from t1;
DELETE FROM t1 WHERE rowid NOT IN
(SELECT MIN(rowid) FROM t1 GROUP BY lastName, firstname
);
1 朱 亮
4 王 小丫
5 张 三