# 表
表：uf_wzlb （物资类别）
表：uf_wzzl （物资种类）
表：uf_wzlb_wzzl （物资类别和物资种类关联表，物资类别与物资种类为一对多关系）
# 情景
如，物资类别编码为
WZLB-201805100004
该物资类别绑定了多个物资种类，绑定的物资种类编码为
WZZL-201805100044,WZZL-201805100015,WZZL-201805100039,WZZL-201805100043,WZZL-201805100041,WZZL-201805100040,WZZL-201805100045,WZZL-201805100030
注意在末尾拼一个逗号，oracle 拼接字符串用 “||”。
SELECT ID, MZ, BH | |
FROM UF_WZZL | |
WHERE BH IN (SELECT REGEXP_SUBSTR((SELECT wzzl | |
FROM uf_wzlb_wzzl | |
WHERE wzlb = 'WZLB-201805100002') || ',', | |
'[^,]+', | |
1, | |
rownum) | |
FROM dual | |
CONNECT BY rownum <= LENGTH(REGEXP_REPLACE((SELECT wzzl | |
FROM uf_wzlb_wzzl | |
WHERE wzlb = | |
'WZLB-201805100002') || ',', | |
'[^,]', | |
NULL))) |
SELECT ID, MZ, BH | |
FROM UF_WZZL | |
WHERE BH IN (SELECT REGEXP_SUBSTR('WZZL-201805100031,WZZL-201805100032,WZZL-201805100033,WZZL-201805100034,WZZL-201805100035,WZZL-201805100030,', | |
'[^,]+', | |
1, | |
rownum) | |
FROM dual | |
CONNECT BY rownum <= LENGTH(REGEXP_REPLACE('WZZL-201805100031,WZZL-201805100032,WZZL-201805100033,WZZL-201805100034,WZZL-201805100035,WZZL-201805100030,', | |
'[^,]', | |
NULL))) |
