原問題
https://www.modb.pro/issue/9491
有一個tag表:
id tag
1 a,b,c
2 a,c,d
3 a
如何查詢 tag 里包含 a 和 c 的愿题？ 也就是第一行和第二行
with cte as
(
select 1 as id,'a,b,c' as tag union all
select 2 as id,'a,c,d' as tag union all
select 3 as id,'a,d,e' as tag union all
select 4 as id,'c,d,e' as tag union all
select 5 as id,'a' as tag
)
select * from cte
where id in
(
select id from (
SELECT id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tag,',',help_topic_id+1),',',-1) AS num
FROM
mysql.help_topic ,cte
WHERE
help_topic_id < LENGTH(tag)-LENGTH(REPLACE(tag,',',''))+1
) as t
where num ='a' or num = 'c'
group by id
having count(*) >=2
)
結(jié)果
id tag
1 a,b,c
2 a,c,d
