Oracle多行记录合并处理

1:效果如下图所示:

表T1:

CREATE TABLE T1

(

  WEEKWORKID VARCHAR2(20) ,

  DD VARCHAR2(20)

)

表T2

CREATE TABLE T2

(

  WEEKWORKID VARCHAR2(20) ,

  NR VARCHAR2(20)

)

SQL语句:

select t2.weekworkid,t2.nr,T.dd

from t2

left 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.weekworkid

order by weekworkid

运行结果: