17,081
社区成员
发帖
与我相关
我的任务
分享
select * from
(select subs_id,
lag(imei)over(partition by subs_id order by eff_dt desc) old_imei,
imei
from CMPDATA_TB_SUBS_IMEI_CHAG_DTL)
WHERE old_imei>0;
select * from
(select subs_id,
max(case when rn=2 then imei end) old_imei,
max(case when rn=1 then imei end) imei
(SELECT subs_id,imei,eff_dt,
row_number()over(partition by subs_id order by eff_dt desc)
FROM CMPDATA_TB_SUBS_IMEI_CHAG_DTL) a
group by subs_id)
where old_imei>0;
给你改了,起码可读性比你的好很多。性能应该也比你的高,虽然改的面目全非了