参考SQL:
select
dbdkcplx,
(select to_char(wm_concat(name))
from dict_dbdkcplx cplx where a.dbdkcplx = cplx.code or a.dbdkcplx like '%, ' || cplx.code || ', %' or a.dbdkcplx like '%, ' || cplx.code or a.dbdkcplx like cplx.code || ', %'
) dbdkcplx1
from gsjk2018.v_proj_particulars_dflx a;
自己用本地表测试:
select dbdkcplx,
(select to_char(wm_concat(cplx.dictname)) from base_dict_entry cplx where
(a.dbdkcplx = cplx.dictid or a.dbdkcplx like '%, ' || cplx.dictid || ', %' or a.dbdkcplx like '%, ' || cplx.dictid or a.dbdkcplx like cplx.dictid || ', %')
and dicttypeid = 'FMS_GUARANTEED_LOANS_TYPES'
) dbdkcplx1
from (select '1, 2, 3' dbdkcplx from dual)a;
根据1,2,3查出的实际对应的值
字典表数据如下:
实际案例参考:
select
(
select to_char(wm_concat(bde.dictname)) from base_dict_entry bde where
(
jsonpkg.getval(pci.contractsummary,'guranteeMethod') = to_char(bde.dictid)
or jsonpkg.getval(pci.contractsummary,'guranteeMethod') like '%, ' || bde.dictid || ', %'
or jsonpkg.getval(pci.contractsummary,'guranteeMethod') like '%, ' || bde.dictid
or jsonpkg.getval(pci.contractsummary,'guranteeMethod') like bde.dictid || ', %'
)
and dicttypeid = 'GURANTEE_METHOD'
)as guranteeMethod
FROM
(
select * from v_proj_baseAndParticipant p WHERE p.BUSSTYPE != '07' and p.PROJSTAGE > 3005
and p.REGUARANT!=1 and p.CHILDBUSSTYPE!='SY_QYZ' and p.BUSSTYPE != '06'
)p
LEFT OUTER JOIN legal_pactset_info lpi on p.PROJID = lpi.PROJID
LEFT OUTER JOIN proj_contract_info pci on pci.contractid = lpi.contractid;
end