oracle 查询所有组合,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