oracle的拆分组合查询

表：msg_content

表：msg_contact_person

想要得到的效果

要点分析：结果表和表1不同的地方是receiver字段加入了表2的真实姓名

实现方式：

   第一步：拆分表1的字段

                SELECT c.msg_content_id AS contentId,
                                       REGEXP_SUBSTR(receiver, '[^;]+', 1, l) AS b
                                  FROM msg_content c,
                                       (SELECT LEVEL l
                                          FROM DUAL
                                        CONNECT BY LEVEL <= 30)
                                 WHERE l <= LENGTH(receiver) -
                                       LENGTH(REPLACE(receiver, ';')) + 1

              得到结果集：

     

第二步：与表2左连接：

     select bb.contentId, bb.b, dd.user_real_name
                          from (SELECT c.msg_content_id AS contentId,
                                       REGEXP_SUBSTR(receiver, '[^;]+', 1, l) AS b
                                  FROM msg_content c,
                                       (SELECT LEVEL l
                                          FROM DUAL
                                        CONNECT BY LEVEL <= 30)
                                 WHERE l <= LENGTH(receiver) -
                                       LENGTH(REPLACE(receiver, ';')) + 1) bb
                          left join msg_contact_person dd on bb.b = dd.account_name
                         where bb.b is not null

结果集：

第四步：用wm_concat进行字符的组合，并且加入分页  OK完成

select *
  from (select dd.msg_content_id,
               dd.account_name,
               cc.receiver,
               dd.sender,
               dd.realname,
               dd.title,
               dd.content,
               dd.send_time,
               dd.if_del,
               dd.msg_lv
          from msg_content dd,
               (select jj.contentId,
                       replace(wm_concat(jj.b ||
                                         decode(jj.user_real_name,
                                                '',
                                                '',
                                                '(' || jj.user_real_name || ')')),
                               ',',
                               ';') || ';' receiver
                  from (select bb.contentId, bb.b, dd.user_real_name
                          from (SELECT c.msg_content_id AS contentId,
                                       REGEXP_SUBSTR(receiver, '[^;]+', 1, l) AS b
                                  FROM msg_content c,
                                       (SELECT LEVEL l
                                          FROM DUAL
                                        CONNECT BY LEVEL <= 30)
                                 WHERE l <= LENGTH(receiver) -
                                       LENGTH(REPLACE(receiver, ';')) + 1) bb
                          left join msg_contact_person dd on bb.b = dd.account_name
                         where bb.b is not null) jj
                 group by jj.contentId) cc
         where dd.msg_content_id = cc.contentId
         order by dd.msg_content_id desc)
 where rownum <= 10