instr应用
select * from a where instr(a,b)>0;用于实现B字段是A字段中的某一部分的时候
临时表+判断
select
case when
instr((listagg(temp.srvtype_code,',')within group(order by temp.srvtype_code)),ts.srvtype_code)>0
then
ts.srvtype_code
else
concat(listagg(temp.srvtype_code,',')within group(order by temp.srvtype_code),concat(',',ts.srvtype_code))
end SERVICECOMPOSE
from
AI_SERVICE t,
AI_PROVIDER ta,
AI_SRVTYPE ts,
(
select sat.service_id,ts.srvtype_code
from ai_srv_and_type sat join AI_SRVTYPE ts on sat.srvtype_id=ts.id
) temp
WHERE t.PARTY_ID = ta.ID
and
temp.service_id=t.id
and
t.srvtype_id=ts.id
group by
t.ID,ts.srvtype_code,ta.PARTY_CODE,ta.PARTY_NAME,t.GROUPSERVICE,t.SERVICE_CREDIT,t.SERVICE_UNICOM_CODE,ts.srvtype_name,
t.SERVICE_CODE,t.SERVICE_NAME,t.BUSSINESS_STATUS,t.INTROURL,t.ACCESSURL,t.WAPINTROPIC,t.TERMTYPECODE,t.EDDDATE,t.ONLINEDATE,
t.CREATETIME,t.EXPDATE,t.CONFIRMURL,t.FREEURL,t.NEEDCONFMBACK,t.NEEDCONFMBACK,t.CHECKTYPE,t.WAPSERVICETYPE,t.SPORDERURL,t.SYNORDERFUNC,
t.SPPSEDOFLAG,ta.CLASSIFY,t.HASSRVTYES,t.id,t.srvtype_id,t.PARTY_ID,t.srvtype_id,t.bussiness_status,service_id,
t.DATARANGE,t.SERVICE_CODE,ta.id,to_char(xmldata),SERVICECOMPOSE