SQL> with u(name,email) as( 2 select '張三','[email protected]' from dual 3 union all select '李四','[email protected]' from dual 4 union all select '王五','[email protected]' from dual 5 union all select '馬六','[email protected]' from dual 6 ) 7 select wm_concat(u.email) from u,( 8 select regexp_substr('張三;李四;王五;馬六', '[^;]+',1,rownum) name from dual 9 connect by rownum<=length('張三;李四;王五;馬六')-length(replace('張三;李四;王五;馬六', ';', ''))+1) t 10 where u.name=t.name;WM_CONCAT(U.EMAIL) -------------------------------------------------------------------------------- [email protected],[email protected],[email protected],[email protected]
能大概解释下这2句吗?知道是正则表达式,但是思路看不懂 select regexp_substr('張三;李四;王五;馬六', '[^;]+',1,rownum) name from dual 9 connect by rownum<=length('張三;李四;王五;馬六')-length(replace('張三;李四;王五;馬六', ';', ''))+1) t
SQL> with u(name,email) as(
2 select '張三','[email protected]' from dual
3 union all select '李四','[email protected]' from dual
4 union all select '王五','[email protected]' from dual
5 union all select '馬六','[email protected]' from dual
6 )
7 select wm_concat(u.email) from u,(
8 select regexp_substr('張三;李四;王五;馬六', '[^;]+',1,rownum) name from dual
9 connect by rownum<=length('張三;李四;王五;馬六')-length(replace('張三;李四;王五;馬六', ';', ''))+1) t
10 where u.name=t.name;WM_CONCAT(U.EMAIL)
--------------------------------------------------------------------------------
[email protected],[email protected],[email protected],[email protected]
select regexp_substr('張三;李四;王五;馬六', '[^;]+',1,rownum) name from dual
9 connect by rownum<=length('張三;李四;王五;馬六')-length(replace('張三;李四;王五;馬六', ';', ''))+1) t