求助一个SQL语句输出理想结果

doctortree 2013-11-09 11:55:55
--事例数据
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

求各位大神指教,在线等,万分感谢!!
...全文
319 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
jdsnhan 2013-11-15
  • 打赏
  • 举报
回复
[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]
mawenpinga 2013-11-15
  • 打赏
  • 举报
回复
建个函数,其实就一个遍历的过程:
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
  • 打赏
  • 举报
回复
都很强大
archwuke1 2013-11-12
  • 打赏
  • 举报
回复
今天突然想到昨天说没有isroot字段不行,其实是太注重connect_by这里的 只要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啥的我还没接触过,先学习下
archwuke1 2013-11-11
  • 打赏
  • 举报
回复
这个不容易啊,是不是要用connect by ,level什么的,这个的用法一直就学不会 只会依葫芦画瓢
大话EPM 2013-11-11
  • 打赏
  • 举报
回复
SELECT MAX(OLDSUBSID) KEEP(DENSE_RANK FIRST ORDER BY LV DESC) OLDSUBSID, 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; 请一个大神指导的,我写出了里面的查询,外层不是很熟悉
archwuke1 2013-11-11
  • 打赏
  • 举报
回复
如果你源表中可以有条件判明一条记录是否为根记录,比如有个isroot字段
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);
否则我还解决不了这个问题,求高手指点
doctortree 2013-11-10
  • 打赏
  • 举报
回复
怎么还没人。。。。
doctortree 2013-11-10
  • 打赏
  • 举报
回复
自己顶一下。。继续等大神

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧