oracle判断手机号长度,表中记录根据证件号,手机号,userid 按照记录的入库时间进行合并...

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;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值