CREATE TABLE T1 (  WEEKWORKID VARCHAR2(20) ,  DD VARCHAR2(20) ) 

CREATE TABLE T2 (  WEEKWORKID VARCHAR2(20) ,  NR VARCHAR2(20) )

select t2.weekworkid,t2.nr,T.ddfrom t2left join (  -- 4筛选结果集  select weekworkid,max(substr(dd,2))dd  from(    -- 3使用sys_connect_by_path生成结果集    select weekworkid,sys_connect_by_path(dd,‘,‘)dd    from(      -- 2创建子节点与父节点      select weekworkid,dd,weekworkid||rn rchild,weekworkid||(rn-1)rfather      from(        -- 1对记录添加序号        select t1.weekworkid,t1.dd,row_number()             over(partition by t1.weekworkid order by t1.dd) rn         from t1        where t1.weekworkid in(          select weekworkid from t2        )      )    )connect by prior rchild = rfather start with rfather like ‘%0‘  ) group by weekworkid)T on t2.weekworkid=T.weekworkidorder by weekworkid