临时表&&instr

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