Query shards and combinations in Oracle

Table: msg_content

Table: msg_contact_person

Expected results

Key Point Analysis: the difference between the result table and table 1 is that the real name of table 2 is added to the "er" field.

Implementation Method:

Step 1: Split the fields in table 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 (worker ER )-
LENGTH (REPLACE (separator er, ';') + 1

Result set:

Step 2: left join with table 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 (worker ER )-
LENGTH (REPLACE (separator er, ';') + 1) bb
Left join msg_contact_person dd on bb. B = dd. account_name
Where bb. B is not null

Result set:

Step 4: Use wm_concat to combine characters and add the page OK

Select *
From (select dd. msg_content_id,
Dd. account_name,
Cc. Cycler,
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 | ')')),
',',
';') | ';' Worker er
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 (worker ER )-
LENGTH (REPLACE (separator er, ';') + 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