with tab1 as (
select '1' id,'11'certi_code,'11' tel,''userid,'2018-04-21 11:23:54' cdate from dual
union all
select '2' id,'11'certi_code,'22' tel,'22'userid,'2018-04-22 11:23:54' cdate from dual
union all
select '3' id,'22'certi_code,'22' tel,'33'userid,'2018-04-23 11:23:54' cdate from dual
union all
select '4' id,'33'certi_code,'44' tel,'33'userid,'2018-04-20 11:23:54' cdate from dual
union all
select '5' id,'11'certi_code,'44' tel,'33'userid,'2018-04-24 11:23:54' cdate from dual
union all
select '6' id,'44'certi_code,'22' tel,'22'userid,'2018-04-25 11:23:54' cdate from dual
union all
select '7' id,'55'certi_code,'66' tel,'33'userid,'2018-04-26 11:23:54' cdate from dual
),
tab2 as (
select *
from tab1 t1,
(select distinct id_1, id_2
from (select distinct t1.id_1, t2.id_2
from (select t1.id id_1, t2.id id_2
from tab1 t1, tab1 t2
where t1.certi_code = t2.certi_code) t1,
(select t1.id id_1, t2.id id_2
from tab1 t1, tab1 t2
where t1.tel = t2.tel
) t2
where t1.id_2 = t2.id_1
union all
select distinct t1.id_1, t2.id_2
from (select t1.id id_1, t2.id id_2
from tab1 t1, tab1 t2
where t1.tel = t2.tel
) t1,
(select t1.id id_1, t2.id id_2
from tab1 t1, tab1 t2
where t1.certi_code = t2.certi_code
) t2
where t1.id_2 = t2.id_1)) t2
where t1.id = t2.id_1
),
tab3 as (
select t1.*, nvl(t2.id_2, t1.id_2) id_2_true
from tab2 t1,
(select distinct t1.id id_1, t2.id_2
from tab1 t1, tab2 t2
where t1.userid = t2.userid
and t1.id != t2.id) t2
where t1.id_2 = t2.id_1(+)
),
tab4 as (
select distinct t1.* from (
select t1.id, t1.id_2 from tab3 t1
union all
select t1.id, t1.id_2_true from tab3 t1) t1)
select t1.a_id, t1.id b_id, t1.cdate_ cdate
from (select t1.*,
t2.*,
first_value(t1.id) over(partition by mx order by t1.cdate desc) a_id,
max(t1.cdate) over(partition by mx) cdate_
from tab1 t1,
(select distinct mx, id_2
from (select t1.id,
t1.id_2,
max(t1.id_2) over(partition by t1.id) mx
from tab4 t1) t1) t2
where t1.id = t2.id_2) t1
where t1.id != t1.a_id
order by t1.id;