SQL Server LEFT JOIN逗号分隔多分类ID实现分类路径拼接方案

适用版本说明 #

• 本方案依赖STRING_SPLIT函数的ordinal参数，仅支持SQL Server 2022及以上版本；低版本可自定义拆分函数返回序号实现相同逻辑。

完整实现代码 #

DECLARE @PRODUCTS TABLE (ID int, DESCRIPTION varchar(50), CAT varchar(30), SUBCAT varchar(30), SUBSUBCAT varchar(30));

INSERT @PRODUCTS (ID, DESCRIPTION, CAT, SUBCAT, SUBSUBCAT) VALUES
(1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),
(2, 'NIKE CORTEZ', '1, 5', '12, 15', '104, 374'),
(3, 'ADIDAS PANTS', '2, 6', '27, 35', '238, 374');

DECLARE @CATS TABLE (ID int, DESCR varchar(100));

INSERT @CATS (ID, DESCR) VALUES
(1, 'MEN'),
(2, 'WOMEN'),
(3, 'UNISEX'),
(4, 'KIDS'),
(5, 'TEENS'),
(6, 'BACK TO SCHOOL');

DECLARE @SUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBCATS (ID, DESCR) VALUES
(10, 'FOOTWEAR'),
(12, 'OUTERWEAR'),
(14, 'SWIMWEAR'),
(15, 'HOODIES'),
(27, 'CLOTHING'),
(35, 'SPORTS');

DECLARE @SUBSUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBSUBCATS (ID, DESCR) VALUES
(100, 'RUNNING'),
(104, 'ZIP TOPS'),
(135, 'FLEECE'),
(157, 'BIKINIS'),
(238, 'PANTS'),
(374, 'JOGGERS');

-- 核心查询逻辑
SELECT 
    prod.ID,
    prod.DESCRIPTION,
    STRING_AGG(CONCAT(c.DESCR, ' > ', sc.DESCR, ' > ', ssc.DESCR), ' @ ') AS CATEGORIES
FROM @PRODUCTS prod
-- 拆分一级分类并保留存储序号
CROSS APPLY STRING_SPLIT(REPLACE(prod.CAT, ' ', ''), ',', 1) c_split
-- 关联同序号的二级分类拆分结果
LEFT JOIN STRING_SPLIT(REPLACE(prod.SUBCAT, ' ', ''), ',', 1) sc_split ON sc_split.ordinal = c_split.ordinal
-- 关联同序号的三级分类拆分结果
LEFT JOIN STRING_SPLIT(REPLACE(prod.SUBSUBCAT, ' ', ''), ',', 1) ssc_split ON ssc_split.ordinal = c_split.ordinal
-- 关联分类表获取分类名称
LEFT JOIN @CATS c ON c.ID = TRY_CAST(c_split.value AS INT)
LEFT JOIN @SUBCATS sc ON sc.ID = TRY_CAST(sc_split.value AS INT)
LEFT JOIN @SUBSUBCATS ssc ON ssc.ID = TRY_CAST(ssc_split.value AS INT)
-- 过滤三个层级分类都存在的有效分组，自动忽略多余ID
WHERE c.DESCR IS NOT NULL AND sc.DESCR IS NOT NULL AND ssc.DESCR IS NOT NULL
GROUP BY prod.ID, prod.DESCRIPTION

实现逻辑说明 #

• 用STRING_SPLIT拆分每个分类字段的逗号分隔ID，通过ordinal参数保留ID的原始存储顺序
• 按序号关联三个分类的拆分结果，保证第N个一级分类对应第N个二级、三级分类
• 用TRY_CAST做类型转换避免非法ID报错，过滤掉缺少任意一级分类的分组，自动忽略数量不匹配的多余ID
• 用STRING_AGG将多个分类三元组用@拼接为最终字符串，完全符合要求的输出格式

内容的提问来源于stack exchange，提问作者Faye D.