比较同表oracle中的数据

设置

create table T1 (id number(10),x nvarchar2(10),y nvarchar2(10));
insert into T1 values(1,'aaa','bbb');
insert into T1 values(2,'xxx','xxx');
insert into T1 values(3,'bbb');
insert into T1 values(4,'ccc','ccc');

打电话

select a.id aid,b.id bid,'same' status  -- all items with matching fields
from 
  (SELEct 'fakEID123' fakEID,t.* FROM t1 t where id = 1) a inner join 
  (SELEct 'fakEID123' fakEID,t.* FROM t1 t where id = 3) b on a.fakEID = b.fakEID
where a.x = b.x and a.y = b.y 
union all
select a.id,b.id,'different' status -- union with all items with not matching fields
from 
  (SELEct 'fakEID123' fakEID,t.* FROM t1 t where id = 2) a inner join 
  (SELEct 'fakEID123' fakEID,t.* FROM t1 t where id = 4) b on a.fakEID = b.fakEID
where a.x <> b.x and a.y <> b.y 

结果

您可以使用 cte 并将每条记录与所有其他记录连接起来进行比较，如下所示：

With cte as
(SELEct id,listagg(item,',') within group (order by item) as items
   From your_table t
  Group by id)
SELEct t1.id,t2.id,Case when t1.items = t2.items then 'match' else 'do not match' end as res_
 From cte t1 join cte t2 on t1.id < t2.id 

SELEct t1.*
  from table t1
  join table t2
    on t1.id != t2.id 
   and t1.t1 = t2.t1
   and t1.t2 = t2.t2
   and t1.t3 = t2.t3

数据输出

重要的是表本身连接并且 ID 不同，以免评估相同的记录。