如何用SQL或ADF实现表中逗号分隔值的关联查询？

假设表结构 #

• 主表 MainTable：包含业务主键（如 ID）、逗号分隔的类别ID串 CategoryIDs、逗号分隔的卖家ID串 SellerIDs
• 类别Lookup表 CategoryLookup：CategoryID（主键）、CategoryName
• 卖家Lookup表 SellerLookup：SellerID（主键）、SellerName

实现SQL语句 #

SELECT
    mt.ID,
    -- 拼接类别名称（保留原ID顺序）
    STRING_AGG(cl.CategoryName, ', ') WITHIN GROUP (ORDER BY CHARINDEX(',' + CAST(cl.CategoryID AS VARCHAR) + ',', ',' + mt.CategoryIDs + ',')) AS CategoryNames,
    -- 拼接卖家名称（保留原ID顺序）
    STRING_AGG(sl.SellerName, ', ') WITHIN GROUP (ORDER BY CHARINDEX(',' + CAST(sl.SellerID AS VARCHAR) + ',', ',' + mt.SellerIDs + ',')) AS SellerNames
FROM MainTable mt
-- 拆分类别ID并关联类别表
CROSS APPLY (
    SELECT CAST(value AS INT) AS CategoryID
    FROM string_split(mt.CategoryIDs, ',')
) cs
LEFT JOIN CategoryLookup cl ON cs.CategoryID = cl.CategoryID
-- 拆分卖家ID并关联卖家表
CROSS APPLY (
    SELECT CAST(value AS INT) AS SellerID
    FROM string_split(mt.SellerIDs, ',')
) ss
LEFT JOIN SellerLookup sl ON ss.SellerID = sl.SellerID
GROUP BY mt.ID, mt.CategoryIDs, mt.SellerIDs

说明 #

• 用string_split+CROSS APPLY把逗号分隔的ID串拆成单独行，替代游标/临时表的行转列逻辑，简洁高效
• LEFT JOIN关联Lookup表，就算主表ID在Lookup表里找不到，也不会丢失主表数据，容错性更好
• STRING_AGG负责把拆分后的名称重新拼成串，加的CHARINDEX排序是关键——SQL Server 2019之前的string_split不保证拆分顺序，这么写能让拼接后的名称顺序和原ID串完全一致，避免乱序
• 只要Lookup表的主键（CategoryID、SellerID）建了索引，这个查询的性能会非常可观，完全不需要临时表或循环逻辑

步骤流程 #

1. 拉取数据源：把主表、类别Lookup表、卖家Lookup表都添加为ADF数据流的数据源
2. 拆分类别ID：给主表添加「拆分列」转换，选择「拆分为行」模式，分隔符设为逗号，将CategoryIDs拆成多行
3. 匹配类别名称：添加「查找」转换，关联类别Lookup表，用拆分后的ID匹配CategoryID，带出对应的CategoryName
4. 合并类别名称：添加「聚合」转换，按主表的业务主键（如ID）分组，用concat_ws(', ', collect(CategoryName))把多个类别名称拼成一个字符串
5. 处理卖家ID：对主表（或已经处理完类别的结果）重复上述拆分、查找、聚合步骤，完成SellerIDs到SellerNames的转换
6. 输出结果：将处理好的最终数据写入目标数据库或存储位置

关键配置 #

• 查找转换：设置为「左外连接」，避免主表数据因无匹配ID丢失；勾选「仅匹配第一个行」可提升处理性能
• 聚合转换：确保分组键选择主表的唯一业务标识，避免数据聚合错误

优势 #

• 可视化配置，无需编写复杂SQL，适合非技术人员操作
• 支持大规模分布式数据处理，能高效应对海量数据场景
• 可与ADF的触发器、管道等组件集成，实现全流程自动化数据同步

内容的提问来源于stack exchange，提问作者Andy