sql多字段模糊查询

悠悠 2022-04-14 03:55 523阅读 0赞

SELECT FPT.PRODUCT\_IMAGE\_PATH AS PRODUCT\_IMAGE\_PATH,  
FIM.ITEM\_NAME AS ITEM\_NAME,  
FIM.ITEM\_NUMBER AS ITEM\_NUMBER,  
FIM.SELF\_ITEM\_CODE AS SELF\_ITEM\_CODE,  
FBIT.PIECE\_JANCODE AS PIECE\_JANCODE,  
FBIT.SPEC\_NAME AS SPEC\_NAME,  
FBIT.SPEC\_BEGIN AS SPEC\_BEGIN,  
TO\_CHAR(REPLACE((SELECT WMSYS.WM\_CONCAT(IF\_KIKAKU.MATCHING\_M.MATCH\_USER\_NAME)  
FROM IF\_KIKAKU.FREE\_ITEM\_M INNER JOIN IF\_KIKAKU.FREE\_USER\_M  
ON IF\_KIKAKU.FREE\_USER\_M.USER\_CD = IF\_KIKAKU.FREE\_ITEM\_M.USER\_CD INNER JOIN  
IF\_KIKAKU.MATCHING\_M ON IF\_KIKAKU.MATCHING\_M.SEQ = IF\_KIKAKU.FREE\_USER\_M.FREE\_FLG\_OWNER\_SEQ  
AND IF\_KIKAKU.MATCHING\_M.USER\_CD = '8002'  
WHERE IF\_KIKAKU.FREE\_ITEM\_M.ITEM\_NUMBER = FIM.ITEM\_NUMBER  
AND (TO\_CHAR(IF\_KIKAKU.FREE\_ITEM\_M.DELETION\_DATE, 'yyyy/mm/dd') =  
'1900/01/01'  
OR IF\_KIKAKU.FREE\_ITEM\_M.DELETION\_DATE IS NULL)  
AND IF\_KIKAKU.MATCHING\_M.DEL\_FLG = '0'  
AND IF\_KIKAKU.FREE\_ITEM\_M.USER\_CD NOT IN (SELECT IF\_KIKAKU.FREE\_USER\_M.USER\_CD  
FROM IF\_KIKAKU.FREE\_USER\_M  
WHERE IF\_KIKAKU.FREE\_USER\_M.FREE\_FLG\_OWNER\_SEQ = 24637)), ',', '、')) AS  
OTHER\_USER\_NAME,  
REPLACE((SELECT TO\_CHAR(WMSYS.WM\_CONCAT(PCT.PRODUCT\_SELLER\_NAME))  
FROM IF\_KIKAKU.PRODUCT\_CUSTOMER\_T PCT  
WHERE PCT.USER\_CD = FIM.USER\_CD  
AND PCT.SELL\_PRODUCT\_CD = FIM.SELF\_ITEM\_CODE  
AND PCT.DEL\_FLG = '0'  
AND PCT.PRODUCT\_SELLER\_CODE IN (SELECT PRODUCT\_SELLER\_CODE  
FROM IF\_KIKAKU.ITEM\_MATCHING\_T  
WHERE SELL\_PRODUCT\_CD = FIM.SELF\_ITEM\_CODE  
AND SPECIFICATION\_CD = FIM.ITEM\_NUMBER)), ',', '、') AS PRODUCT\_SELLER\_NAME,  
FIM.MEMO AS MEMO,  
FIM.ITEM\_KBN AS ITEM\_KBN  
FROM IF\_KIKAKU.FREE\_ITEM\_M FIM LEFT JOIN IF\_KIKAKU.FREE\_BASIC\_INFORMATION\_T  
FBIT  
ON FBIT.USER\_CD = FIM.USER\_CD  
AND FBIT.ITEM\_NUMBER = FIM.ITEM\_NUMBER LEFT JOIN IF\_KIKAKU.FREE\_PACKING\_T FPT  
ON FPT.USER\_CD = FIM.USER\_CD  
AND FPT.ITEM\_NUMBER = FIM.ITEM\_NUMBER  
AND (FPT.DEL\_DATE = '1900/01/01'  
OR FPT.DEL\_DATE IS NULL)  
WHERE FIM.USER\_CD = '8002'  
AND (FIM.DELETION\_DATE = '1900/01/01'  
OR FIM.DELETION\_DATE IS NULL)  
AND (FIM.ITEM\_NAME LIKE ('%3329AB0D%')  
OR FIM.ITEM\_NUMBER LIKE ('%3329AB0D%')  
OR FIM.SELF\_ITEM\_CODE LIKE ('%3329AB0D%')  
OR FBIT.PIECE\_JANCODE LIKE ('%3329AB0D%')  
OR (REPLACE((SELECT TO\_CHAR(WMSYS.WM\_CONCAT(MATCH\_USER\_NAME))  
FROM IF\_KIKAKU.MATCHING\_M INNER JOIN IF\_KIKAKU.FREE\_USER\_M  
ON IF\_KIKAKU.FREE\_USER\_M.FREE\_FLG\_OWNER\_SEQ = IF\_KIKAKU.MATCHING\_M.SEQ  
INNER JOIN IF\_KIKAKU.FREE\_ITEM\_M ON IF\_KIKAKU.FREE\_ITEM\_M.USER\_CD =  
IF\_KIKAKU.FREE\_USER\_M.USER\_CD  
AND (IF\_KIKAKU.FREE\_ITEM\_M.DELETION\_DATE = '1900/01/01'  
OR IF\_KIKAKU.FREE\_ITEM\_M.DELETION\_DATE IS NULL)  
WHERE IF\_KIKAKU.FREE\_ITEM\_M.ITEM\_NUMBER = FIM.ITEM\_NUMBER  
AND (IF\_KIKAKU.FREE\_ITEM\_M.DELETION\_DATE = '1900/01/01'  
OR IF\_KIKAKU.FREE\_ITEM\_M.DELETION\_DATE IS NULL)  
AND IF\_KIKAKU.MATCHING\_M.DEL\_FLG = 0), ',', '、')) LIKE ('%3329AB0D%'))  
ORDER BY FIM.SELF\_ITEM\_CODE DESC;