sql求助

[qsc1818518]

A表三个字段 id,phone,time 需要根据时间判断用户的手机号变化
形成id,odl_id,new_id 但是假如一个用户换了三个手机号，A-B-C 只需要A-B和B-C的手机号变化

[qsc1818518]

[js14982]

听的不是很明白，你试试下面这个语句看是否满足你要求

select * from 
(select phone,time,lag(id)over(partition by phone order by time) as old_id,id as new_id
from A )
where old_id is not null;

[qsc1818518]

引用 9 楼 qq_29312279 的回复:

select id, newphone, oldphone from (select id, phone as newphone, (select phone from testa c where c.date1 = (select max(date1) from testa b where b.date1 < a.date1 and c.id = b.id) and c.id = a.id) as oldphone from testa a) where oldphone is not null

你这取最大的手机号还是有问题，并不会根据时间变化取数，而是取手机号的最大值

[qsc1818518]

A表三个字段 id,phone,time 需要根据时间判断用户的手机号变化 形成id,odl_phone,new_phone 但是假如一个用户换了三个手机号，A-B-C 只需要A-B和B-C的手机号变化,并不需要A-C的数据， 我现在写的语句多了A-C的手机号变化的数据

[kingkingzhu]

2楼可以实现你的需求

[qsc1818518]

三楼实现不了

[qsc1818518]

SELECT a.subs_id, a.phone ,a.new_phone FROM ( SELECT a.subs_id, a.phone , CASE WHEN a.time<b.time THEN b.phone END new_phone FROM CMPDATA_TB_SUBS_IMEI_CHAG_DTL a, CMPDATA_TB_SUBS_IMEI_CHAG_DTL b WHERE a.subs_id=b.subs_id )a WHERE a.new_imei>0; 这是我写的多了A-C的记录

[kingkingzhu]

with t as ( select 'A' as phone,1 as times from dual union all select 'B' as phone,2 as times from dual union all select 'C' as phone,3 as times from dual ) select phone||'-'||phone1 from ( select phone,lead(phone) over(order by times) as phone1 from t)t1 where phone1 is not null

[2跬步9]

select id ,newphone, oldphone from (select id ,phone as newphone,(select max(phone) from testa b where b.date1 < a.date1) as oldphone from testa a)where oldphone is not null

[2跬步9]

select id, newphone, oldphone from (select id, phone as newphone, (select phone from testa c where c.date1 = (select max(date1) from testa b where b.date1 < a.date1 and c.id = b.id) and c.id = a.id) as oldphone from testa a) where oldphone is not null