1、想从1 个表的1个字段(devTids)中查找出包包含字符的记录
devTids字段的示例记录1条:【rfidtid,devcode】
【E2801191200068FC64650304,实验室0.4kv安全带#01】;【E2806894200050010B93C473,123456】;【E2806894200050010B93C476,安全帽#04】;【E28068942000501D5D241D80,实验室10kv接地线#001】;【E28068942000502A4687958D,安全帽#06】;【E28069952000500433F08588,T2实验室0.4kv安全带#01】;【E280F3362000F00000B05094,实验室0.4kv安全帽#01】;
想找出包含devcode包含‘12’的记录 ===> devTids like '%12%'
表:op_tools_events(目标)
dev_tools 、discard_dev_tools (工具表)
查询sql:
select id,devTids from op_tools_events where id in
(
select DISTINCT id from
(
select id,substr(word, 2, instr(word, ',')-2) as word1 from (
WITH split(word, str,id) AS (
select '',devTids,id from op_tools_events where devTids like '%12%'
UNION all SELECT
substr(str, 0, instr(str, ';')),
substr(str, instr(str, ';')+1),
id
FROM split WHERE str!=''
) SELECT word,id FROM split WHERE word!=''
)
WHERE word1 in (
select rfidtid from dev_tools where devcode like '%12%'
UNION
select rfidtid from discard_dev_tools where devcode like '%12%'
)
)
)
order by id