今天上午,在qq上碰到一朋友询问:如何清理掉除id 列外的所有列有重复的数据,思前想后,终于搞定.
源表数据:
SQL> select * from tab_test;
C1 C2 C3
-- --- ---
1 aa aa
2 bb aa
3 cc bb
4 dd bb
5 dd ee
6 ee ff
-- --- ---
1 aa aa
2 bb aa
3 cc bb
4 dd bb
5 dd ee
6 ee ff
最终要得到的结果如下:
目标表:
SQL> select * from tab_test1;
SQL> select * from tab_test1;
C1 C2 C3
-- --- ---
1 aa aa
3 cc bb
5 dd ee
6 ee ff
-- --- ---
1 aa aa
3 cc bb
5 dd ee
6 ee ff
这里关键是记录5,它应该是非重复数据,因为记录4已经是重复数据了,不在考虑之内.
测试代码如下:
truncate table tab_test1;
declare
cursor get_yhbh is select c1,c2,c3 from tab_test where c1>1 order by c1;
v_c1 VARCHAR2(1);
v_c2 VARCHAR2(3);
v_c3 VARCHAR2(3);
aaa number(10);
begin
insert into tab_test1 select * from tab_test where c1=1;
commit;
Open get_yhbh;
loop
fetch get_yhbh into v_c1,v_c2,v_c3;
exit when get_yhbh%notfound;
select count(*) into aaa from tab_test1 where (c2=v_c2 or c3=v_c3);
if aaa=0 then
insert into tab_test1 select * from tab_test where c1=v_c1;
end if;
commit;
end loop;
Close get_yhbh;
end;
/
declare
cursor get_yhbh is select c1,c2,c3 from tab_test where c1>1 order by c1;
v_c1 VARCHAR2(1);
v_c2 VARCHAR2(3);
v_c3 VARCHAR2(3);
aaa number(10);
begin
insert into tab_test1 select * from tab_test where c1=1;
commit;
Open get_yhbh;
loop
fetch get_yhbh into v_c1,v_c2,v_c3;
exit when get_yhbh%notfound;
select count(*) into aaa from tab_test1 where (c2=v_c2 or c3=v_c3);
if aaa=0 then
insert into tab_test1 select * from tab_test where c1=v_c1;
end if;
commit;
end loop;
Close get_yhbh;
end;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/38006/viewspace-667599/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
