--用逗号分割单个字符串;
--示例:
select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i'), names
from (select '1,2,3' nums, 'denggh' names from dual) q
connect by rownum <=
LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1;
--用逗号分割变量
--示例:
select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') as nums_adno
from (select adno nums from IB_CNT_ADPLAYRULE_INFO where adno like '%,%') q
connect by rownum <=
LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1
--用逗号分割一行变量字段,查询数据
select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums,
PLAYRULEID,
DEVICENO,
ADNO,
PLAYSTYLE,
WORDSTYLE,
PICTURESTYLE,
VOICESTYLE,
VIDEOSTYLE,
FILESPACETIME,
ADSPACETIME,
HOMEPLAYFLAG,
HOMESPACETIME,
DEFAULTFLAG,
REMARK,
DEVICEMODEL,
DEVICETYPE
from (select adno nums,
PLAYRULEID,
DEVICENO,
ADNO,
PLAYSTYLE,
WORDSTYLE,
PICTURESTYLE,
VOICESTYLE,
VIDEOSTYLE,
FILESPACETIME,
ADSPACETIME,
HOMEPLAYFLAG,
HOMESPACETIME,
DEFAULTFLAG,
REMARK,
DEVICEMODEL,
DEVICETYPE
from IB_CNT_ADPLAYRULE_INFO
where adno like '%,%') q
connect by rownum <=
LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1
--使用with as 方法(与union all错误)
with a as
(select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums,
PLAYRULEID,
DEVICENO,
ADNO,
PLAYSTYLE,
WORDSTYLE,
PICTURESTYLE,
VOICESTYLE,
VIDEOSTYLE,
FILESPACETIME,
ADSPACETIME,
HOMEPLAYFLAG,
HOMESPACETIME,
DEFAULTFLAG,
REMARK,
DEVICEMODEL,
DEVICETYPE
from (select adno nums,
PLAYRULEID,
DEVICENO,
ADNO,
PLAYSTYLE,
WORDSTYLE,
PICTURESTYLE,
VOICESTYLE,
VIDEOSTYLE,
FILESPACETIME,
ADSPACETIME,
HOMEPLAYFLAG,
HOMESPACETIME,
DEFAULTFLAG,
REMARK,
DEVICEMODEL,
DEVICETYPE
from IB_CNT_ADPLAYRULE_INFO
where adno like '%,%') q
connect by rownum <=
LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1)
SELECT a.PLAYRULEID,
a.DEVICENO,
a.ADNO,
a.PLAYSTYLE,
a.WORDSTYLE,
a.PICTURESTYLE,
a.VOICESTYLE,
a.VIDEOSTYLE,
a.FILESPACETIME,
a.ADSPACETIME,
a.HOMEPLAYFLAG,
a.HOMESPACETIME,
a.DEFAULTFLAG,
a.REMARK,
a.DEVICEMODEL,
a.DEVICETYPE,
b.ADNAME,
b.ADCONTENT,
b.ADTYPE
FROM a, IB_CNT_AD_INFO b
where a.nums in (b.adno)
union
with c as (SELECT wmsys.wm_concat(d.ADCONTENT) ADCONTENT
FROM (select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums
from (select adno nums
from IB_CNT_ADPLAYRULE_INFO
where adno like '%,%') q
connect by rownum <=
LENGTH(q.nums) -
LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1) a,
IB_CNT_AD_INFO d
where a.nums in (d.adno))
select REPLACE(c.ADCONTENT, ',', '') from c
--使用嵌套方法进行查询
select n.PLAYRULEID,
n.DEVICENO,
n.ADNO,
n.PLAYSTYLE,
n.WORDSTYLE,
n.PICTURESTYLE,
n.VOICESTYLE,
n.VIDEOSTYLE,
n.FILESPACETIME,
n.ADSPACETIME,
n.HOMEPLAYFLAG,
n.HOMESPACETIME,
n.DEFAULTFLAG,
n.REMARK,
n.DEVICEMODEL,
n.DEVICETYPE,
n.ADNAME,
m.ADCONTENT,
n.ADTYPE
from (select REPLACE(c.ADCONTENT, ',', '') as ADCONTENT,
c.playruleid as c_playruleid
from (SELECT playruleid, wmsys.wm_concat(d.ADCONTENT) ADCONTENT
FROM (select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums,
playruleid
from (select adno nums, playruleid
from IB_CNT_ADPLAYRULE_INFO
where adno like '%,%') q
connect by rownum <=
LENGTH(q.nums) -
LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1) a,
IB_CNT_AD_INFO d
where a.nums in (d.adno)
group by playruleid) c) m,
(SELECT a.PLAYRULEID,
a.DEVICENO,
a.ADNO,
a.PLAYSTYLE,
a.WORDSTYLE,
a.PICTURESTYLE,
a.VOICESTYLE,
a.VIDEOSTYLE,
a.FILESPACETIME,
a.ADSPACETIME,
a.HOMEPLAYFLAG,
a.HOMESPACETIME,
a.DEFAULTFLAG,
a.REMARK,
a.DEVICEMODEL,
a.DEVICETYPE,
b.ADNAME,
b.ADCONTENT,
b.ADTYPE
FROM (select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums,
PLAYRULEID,
DEVICENO,
ADNO,
PLAYSTYLE,
WORDSTYLE,
PICTURESTYLE,
VOICESTYLE,
VIDEOSTYLE,
FILESPACETIME,
ADSPACETIME,
HOMEPLAYFLAG,
HOMESPACETIME,
DEFAULTFLAG,
REMARK,
DEVICEMODEL,
DEVICETYPE
from (select adno nums,
PLAYRULEID,
DEVICENO,
ADNO,
PLAYSTYLE,
WORDSTYLE,
PICTURESTYLE,
VOICESTYLE,
VIDEOSTYLE,
FILESPACETIME,
ADSPACETIME,
HOMEPLAYFLAG,
HOMESPACETIME,
DEFAULTFLAG,
REMARK,
DEVICEMODEL,
DEVICETYPE
from IB_CNT_ADPLAYRULE_INFO
where adno like '%,%') q
connect by rownum <=
LENGTH(q.nums) -
LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1) a,
IB_CNT_AD_INFO b
where a.nums in (b.adno)
order by b.ADNO) n
--多行数据进行合并:oracle 10g以上 一个wm_concat
select id,wm_concat(name) name from test group by id;
参考博客:
https://blog.csdn.net/seandba/article/details/72669074
https://blog.csdn.net/u014514528/article/details/80611128
https://www.cnblogs.com/MoMo1002/p/6594832.html
https://www.cnblogs.com/xieon1986/archive/2013/01/16/2863077.html