oracle 两字段重复

select max(aa.a),max(aa.b),max(aa.c),max(aa.d) from (select max(t1.fnumber) a,max(t1.name) b,
       substr(to_char(tim, 'yyyy-mm-dd hh24:mi:ss'), 1, 13) c,
       count(*) d
  from jiaban t
  left join tperson t1 on t.personid = t1.id
 group by personid, tim
having count(*) > 1) aa group by aa.a having count(*)>1
union all
select max(aa.a),max(aa.b),max(aa.c),max(aa.d) from (select max(t1.fnumber) a,max(t1.name) b,
       substr(to_char(tim, 'yyyy-mm-dd hh24:mi:ss'), 1, 13) c,
       count(*) d
  from jiaban t
  left join tperson t1 on t.personid = t1.id
 group by personid, tim
having count(*) > 1) aa group by aa.a having count(*)=1

 

显示三个字段完全相同的数据

select p1.*

　　from persons p1,persons p2

　　where p1.id<>p2.id

　　and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address