SQL中如何从逗号分隔存储的ID查询匹配得到对应拼接的名称

问题原因 #

现有SQL未做聚合处理，单条tbb记录匹配到多条tba记录时会拆分为多行输出，不符合预期的单行展示要求。

调整后的SQL（SQL Server 2017+/Azure SQL 版本） #

with tba as(
    select 1 as t1id, 'n1' as name1 union all
    select 2 as t1id, 'n2'  union all
    select 3 as t1id, 'n3'  union all
    select 4 as t1id, 'n4'  
),
tbb as(
    select 1 as t2id, 'nb1' as texts, '1,2,' as t1id union all
    select 2 as t2id, 'nb2' as texts, '3,4,' union all
    select 3 as t2id, 'nb3' as texts, '' 
)
select 
    t2.t2id,
    NULLIF(t2.t1id, '') as t1id,
    t2.texts,
    STRING_AGG(t1.name1, ',') WITHIN GROUP (ORDER BY t1.t1id) as name1
from tbb t2 
left join tba t1 on ','+t2.t1id+',' LIKE '%,'+CONVERT(VARCHAR(50),t1.t1id)+',%'
group by t2.t2id, t2.t1id, t2.texts
order by t2.t2id

低版本SQL Server适配写法（不支持STRING_AGG的场景） #

with tba as(
    select 1 as t1id, 'n1' as name1 union all
    select 2 as t1id, 'n2'  union all
    select 3 as t1id, 'n3'  union all
    select 4 as t1id, 'n4'  
),
tbb as(
    select 1 as t2id, 'nb1' as texts, '1,2,' as t1id union all
    select 2 as t2id, 'nb2' as texts, '3,4,' union all
    select 3 as t2id, 'nb3' as texts, '' 
)
select 
    t2.t2id,
    NULLIF(t2.t1id, '') as t1id,
    t2.texts,
    STUFF((
        select ',' + t1.name1 
        from tba t1 
        where ','+t2.t1id+',' LIKE '%,'+CONVERT(VARCHAR(50),t1.t1id)+',%'
        order by t1.t1id
        for xml path(''), type
    ).value('.', 'varchar(100)'), 1, 1, '') as name1
from tbb t2
order by t2.t2id

MySQL版本适配写法 #

with tba as(
    select 1 as t1id, 'n1' as name1 union all
    select 2 as t1id, 'n2'  union all
    select 3 as t1id, 'n3'  union all
    select 4 as t1id, 'n4'  
),
tbb as(
    select 1 as t2id, 'nb1' as texts, '1,2,' as t1id union all
    select 2 as t2id, 'nb2' as texts, '3,4,' union all
    select 3 as t2id, 'nb3' as texts, '' 
)
select 
    t2.t2id,
    NULLIF(t2.t1id, '') as t1id,
    t2.texts,
    GROUP_CONCAT(t1.name1 order by t1.t1id separator ',') as name1
from tbb t2 
left join tba t1 on CONCAT(',',t2.t1id,',') LIKE CONCAT('%,',t1.t1id,',%')
group by t2.t2id, t2.t1id, t2.texts
order by t2.t2id

核心逻辑说明 #

• 按t2.t2id、t2.t1id、t2.texts分组，保证输出行数和tbb原表行数一致
• 用NULLIF将原表中t1id的空字符串转为预期的NULL值
• 用对应数据库的字符串聚合函数，将匹配到的多个name1按t1id顺序拼接为逗号分隔的字符串

内容的提问来源于stack exchange，提问作者Hong Van Vit