介绍
FIND_IN_SET(str,strlist):返回str在strlist中所在的位置,没有返回0。注:strlist必须以逗号分隔。
数据准备
select 'a' a,'1,2' b
union all
select 'b' a,'1,2,3' b
select 1 id,'t1' v
union ALL
select 2 id,'t2' v
union ALL
select 3 id,'t3' v
将上表转为以下结果
实现sql
select t1.a,t2.id
from
(
select 'a' a,'1,2' b
union all
select 'b' a,'1,2,3' b
) t1
LEFT JOIN
(
select 1 id,'t1' v
union ALL
select 2 id,'t2' v
union ALL
select 3 id,'t3' v
)t2 on FIND_IN_SET(t2.id,t1.b) > 0
ORDER BY t1.a;
最终结果