一.可以在那张表的所有字段上创建唯一索引,通过异常来获得含有相同值的rowid,然后关联原表来实现.
--生成测试表及测试数据
SQL> create table test(id int,id1 int,id2 int,id3 int,id4 int,id5 int,id6 int,id7 int ,id8 int,id9 int,id10 int);
Table created
SQL> insert into test values(1,2,3,4,5,6,7,8,9,10,11);
1 row inserted
SQL> insert into test values(1,2,3,4,5,6,7,8,9,10,11);
1 row inserted
SQL> insert into test values(2,3,4,5,6,7,8,9,10,11,12);
1 row inserted
SQL> insert into test values(2,3,4,5,6,7,8,9,10,11,12);
1 row inserted
SQL> insert into test values(3,4,5,6,7,8,9,10,11,12,13);
1 row inserted
SQL> commit;
Commit complete
--创建异常表
SQL> create table exceptions(row_id rowid,
2 owner varchar2(30),
3 table_name varchar2(30),
4 constraint varchar2(30));
Table created
SQL> alter table test add constraint uk_test unique(id,id1,id2,id3,id4,id5,id6,id7,id8,id9,id10) exceptions into exceptions;
alter table test add constraint uk_test unique(id,id1,id2,id3,id4,id5,id6,id7,id8,id9,id10) exceptions into exceptions
ORA-02299: 无法验证 (MANAGER.UK_TEST) - 未找到重复关键字
--获得含有完全相同的行的记录
(引用自 pydwh)
二.用分析函数
row_number() over (partition by 字段１，字段２，字段３，到字段最后一条Ｎ）rn
然后rn>1看看有没有值。
三.用group by
[@more@]来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/795108/viewspace-926000/ ，如需转载，请注明出处，否则将追究法律责任。
