17,078
社区成员
发帖
与我相关
我的任务
分享
[code=sql]select distinct oldsubsid,
first_value(tid) over(partition by oldsubsid order by lv desc)
from (select oldsubsid, connect_by_root newsubsid tid, level lv
from test1
where CONNECT_BY_ISLEAF = 1
connect by nocycle prior oldsubsid = newsubsid)
[/code]CREATE OR REPLACE FUNCTION findnewsubsid(old_subsid in VARCHAR2)
RETURN VARCHAR as
m_count NUMBER(4):=0;
subsid2 varchar(10);
BEGIN
select newsubsid into subsid2 from test1 where oldsubsid =old_subsid;
loop
select count(*) into m_count from test1 where oldsubsid =subsid2;
if m_count = 1 then
select newsubsid into subsid2 from test1 where oldsubsid =subsid2;
else
return subsid2;
exit;
end if;
end loop;
END;
select b.oldsubsid, findnewsubsid(b.oldsubsid) newsubsid
from test1 b
where oldsubsid in (select oldsubsid from test1
minus
select newsubsid from test1);
OLDSUBSID NEWSUBSID
---------- -------------------------------------------------------
668001 668005
668002 668044
668003 668017
with t1 as (
SELECT connect_by_root oldid rootid ,level lv ,newid
FROM t3
connect by prior newid= oldid
)
SELECT rootid,newid from t1
WHERE 1=1
and not exists (SELECT 1 from t3 WHERE t1.rootid=t3.newid)
and not exists (SELECT 1 from t1 t2 WHERE t1.rootid=t2.rootid and t2.lv>t1.lv);
大神写的的keep啥的我还没接触过,先学习下with t1 as (
SELECT connect_by_root oldid rootid ,t3.*, connect_by_isleaf il,SYS_CONNECT_BY_PATH(oldid, '/') "Path" ,level lv
FROM t3
connect by prior newid= oldid
)
SELECT rootid,newid endid from t1
WHERE 1=1
and rootid in (SELECT oldid from t3 WHERE isroot=1)
and not exists (SELECT 1 from t1 t2 WHERE t1.rootid=t2.rootid and t2.lv>t1.lv);
否则我还解决不了这个问题,求高手指点