数组字段映射(find_in_set()、charindex())的实现#

需求:

Mssql的实现方法#

with tb as (
select
        a.*, lesson as lessonname
from
        t2 a,
        t1 b
where
        charindex(','+b.id+',',','+lessonid+',')>0
)

select id,name,lessonid,    
       [val]=stuff( (select ','+[lessonname]     
                     from tb as kb     
                     where kb.id = ka.id     
                     for xml path('')) , 1 , 1 , '' )    
from tb as ka      
group by id,name,lessonid

Mysql的实现方法#

select
        a.*, group_concat(lesson) as LESSONNAME
from
        t2 a,
        t1 b
where
        find_in_set(b.id, lessonid)
group by
        name