如果关联查询A中对应B表的内容行,一般会用select * from B id in(…);但是由于1中描述的问题,B id不能直接in,需要将A表b字段进行转换,即单行数据转多行数据,以适用于in的查询语法。
解决语法:基本语法:
SELECT REGEXP_SUBSTR ('1,2,3', '[^,]+', 1,rownum)
from dual connect by rownum<=LENGTH ('1,2,3') - LENGTH (regexp_replace('1,2,3', ',', ''))+1;
select selectitem_nm,matter_id from fwjc_matter_selectitem where matter_id in (
SELECT REGEXP_SUBSTR ((MATTER_ID), '[^,]+', 1,rownum) from
(SELECT matter_id from Fwjc_Evaluate_Project where PROJECT_ID=#para(projectId) and project_std = '1'
and end_date between #para(startTime) and
#para(endTime)
and begin_date between #para(startTime) and
#para(endTime)
and update_user=#para(userCode))
connect by rownum<=LENGTH (MATTER_ID)- LENGTH (regexp_replace(MATTER_ID, ',', ''))+1
)