--事例数据
drop table test1 purge;
create table test1
(
oldsubsid varchar2(10),
newsubsid varchar2(10)
);insert into test1 values('668001','668009');
insert into test1 values('668009','668005');
insert into test1 values('668002','668010');
insert into test1 values('668010','668019');
insert into test1 values('668019','668044');
insert into test1 values('668003','668017');
commit;select * from test1;
oldsubsid newsubsid
1 668001 668009
2 668009 668005
3 668002 668010
4 668010 668019
5 668019 668044
6 668003 668017--注:oldsubsid与newsubsid是绝对唯一的--需求:输出OLDSUBSID的最后NEWSUBSID,或者说最后有效的NEWSUBSID的原始OLDSUBSID--目标理想结果
668001 668005
668002 668044
668003 668017求各位大神指教,在线等,万分感谢!!
drop table test1 purge;
create table test1
(
oldsubsid varchar2(10),
newsubsid varchar2(10)
);insert into test1 values('668001','668009');
insert into test1 values('668009','668005');
insert into test1 values('668002','668010');
insert into test1 values('668010','668019');
insert into test1 values('668019','668044');
insert into test1 values('668003','668017');
commit;select * from test1;
oldsubsid newsubsid
1 668001 668009
2 668009 668005
3 668002 668010
4 668010 668019
5 668019 668044
6 668003 668017--注:oldsubsid与newsubsid是绝对唯一的--需求:输出OLDSUBSID的最后NEWSUBSID,或者说最后有效的NEWSUBSID的原始OLDSUBSID--目标理想结果
668001 668005
668002 668044
668003 668017求各位大神指教,在线等,万分感谢!!
只会依葫芦画瓢
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);否则我还解决不了这个问题,求高手指点
NEWSUBSID
FROM (SELECT MAX(CONNECT_BY_ROOT(OLDSUBSID)) OLDSUBSID,
MAX(NEWSUBSID) KEEP(DENSE_RANK FIRST ORDER BY LEVEL DESC) NEWSUBSID,
MAX(LEVEL) LV
FROM TEST11
CONNECT BY PRIOR NEWSUBSID = OLDSUBSID
GROUP BY CONNECT_BY_ROOT(OLDSUBSID))
GROUP BY NEWSUBSID;
请一个大神指导的,我写出了里面的查询,外层不是很熟悉
只要oldid没有在newid里出现过的,就是rootid,所以又重写了一下
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啥的我还没接触过,先学习下
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
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]