mysql 求字符串全包含的写法 即'1,2,3,4'全包含 '1,3,4' 但不全包含 '1,2,5'

先构造测试数据

with cte1 as
(
select 1 as aid,'1,2,3,4' as str
union all
select 2 as aid,'10,11,12,13,14'
)
,cte2 as 
(
select 1 as aid,1 as bid,'1,3,4' as str union all
select 1,2 as bid,'1,2,5' union all
select 2,3 as bid,'10,15'
)
复制

根据需求 在cte2中找出 cte1.aid = cte2.aid 且 cte1.str全包含cte2.str的串，则是要找出aid = 1,bid = 1,str = ‘1,3,4’ 这行数据

第一种方法。 用 8.0特性横向派生表来解决

过程还是用公用表达式，一步一步往下写，条理会更清淅

with cte1 as
(
select 1 as aid,'1,2,3,4' as str
union all
select 2 as aid,'10,11,12,13,14'
)
,cte2 as 
(
select 1 as aid,1 as bid,'1,3,4' as str union all
select 1,2 as bid,'1,2,5' union all
select 2,3 as bid,'10,15'
)
,cte3 as
(
select  aid,bid,substring_index(substring_index(cte2.str,',',help_topic_id + 1),',',-1) as strid
from mysql.help_topic,cte2
where help_topic_id < LENGTH(cte2.str) - length(replace(cte2.str,',','')) + 1
)
SELECT * FROM CTE3
复制

第一步cte1与cte2是构造测试数据的语句。

第二步是cte3 将cte2的字符串拆分。
aid bid strid
1 1 1
1 1 3
1 1 4
1 2 1
1 2 2
1 2 5
2 3 10
2 3 15

with cte1 as
(
select 1 as aid,'1,2,3,4' as str
union all
select 2 as aid,'10,11,12,13,14'
)
,cte2 as 
(
select 1 as aid,1 as bid,'1,3,4' as str union all
select 1,2 as bid,'1,2,5' union all
select 2,3 as bid,'10,15'
)
,cte3 as
(
select  aid,bid,substring_index(substring_index(cte2.str,',',help_topic_id + 1),',',-1) as strid
from mysql.help_topic,cte2
where help_topic_id < LENGTH(cte2.str) - length(replace(cte2.str,',','')) + 1
)

,cte4 as
(
select t.* from  cte1
inner join lateral 
(
 select aid,bid,count(*) as cnt from cte3 
 where POSITION(CONCAT(',',cte3.strid,',') in CONCAT(',',cte1.str,',')) >0  and cte3.aid = cte1.aid
 group by aid,bid
  having cnt >= (select count(*) from cte3 as t where  t.aid = cte3.aid and t.bid = cte3.bid)
) as t
)
SELECT * FROM CTE4
复制

CTE4是第三步。利用横向派生表。找出全包含的记录
aid bid cnt
1 1 3

最终语句

with cte1 as
(
select 1 as aid,'1,2,3,4' as str
union all
select 2 as aid,'10,11,12,13,14'
)
,cte2 as 
(
select 1 as aid,1 as bid,'1,3,4' as str union all
select 1,2 as bid,'1,2,5' union all
select 2,3 as bid,'10,15'
)
,cte3 as
(
	select  aid,bid,substring_index(substring_index(cte2.str,',',help_topic_id + 1),',',-1) as strid
	from mysql.help_topic,cte2
	where help_topic_id < LENGTH(cte2.str) - length(replace(cte2.str,',','')) + 1
)

,cte4 as
(
	select t.* from  cte1
	inner join lateral 
	(
	 select aid,bid,count(*) as cnt from cte3 
	 where POSITION(CONCAT(',',cte3.strid,',') in CONCAT(',',cte1.str,',')) >0  and cte3.aid = cte1.aid
	 group by aid,bid
	  having cnt >= (select count(*) from cte3 as t where  t.aid = cte3.aid and t.bid = cte3.bid)
	) as t
)
select cte2.* from cte2
inner join cte4 on cte4.aid = cte2.aid and cte4.bid = cte2.bid

复制

aid bid str
1 1 1,3,4

第二种方法 就不拆开讲了。
直接贴SQL

with cte1 as
(
select 1 as aid,'1,2,3,4' as str
union all
select 2 as aid,'10,11,12,13,14'
)
,cte2 as 
(
select 1 as aid,1 as bid,'1,3,4' as str union all
select 1,2 as bid,'1,2,5' union all
select 2,3 as bid,'10,15'
)
,cte3 as
(
	select  aid,bid,substring_index(substring_index(cte2.str,',',help_topic_id + 1),',',-1) as strid
	from mysql.help_topic,cte2
	where help_topic_id < LENGTH(cte2.str) - length(replace(cte2.str,',','')) + 1
)
,cte4 as
(
	select  aid,substring_index(substring_index(cte1.str,',',help_topic_id + 1),',',-1) as strid
	from mysql.help_topic,cte1
	where help_topic_id < LENGTH(cte1.str) - length(replace(cte1.str,',','')) + 1
)
select * from cte2 where (aid,bid) not in 
(
   select aid,bid from cte3 where not exists(select 1 from cte4 where cte3.aid = cte4.aid and cte3.strid = cte4.strid)
)

复制

aid bid str
1 1 1,3,4