一张分类表
编码 内容
01 a
02 b
03 c
04 d
05 e
测试数据表
id 内容
1 01,05
2 02
3 01,03
4 02,05
5 01,02,03
6 01,02,04,05
7 02,04
数据结果
id 内容
1 a,e
2 b
3 a,c
4 b,e
5 a,b,c
6 a,b,d,e
7 b,d
------解决方案--------------------
- SQL code
--> 测试数据：[A1]if object_id('[A1]') is not null drop table [A1]create table [A1]([编码] varchar(2),[内容] varchar(1))insert [A1]select '01','a' union allselect '02','b' union allselect '03','c' union allselect '04','d' union allselect '05','e'--> 测试数据：[B2]if object_id('[B2]') is not null drop table [B2]create table [B2]([id] int,[内容] varchar(11))insert [B2]select 1,'01,05' union allselect 2,'02' union allselect 3,'01,03' union allselect 4,'02,05' union allselect 5,'01,02,03' union allselect 6,'01,02,04,05' union allselect 7,'02,04'gowith tas(select b.id, a.内容 from [B2] binner join [A1] aon CHARINDEX(a.编码,b.内容)>0)select a.id, 内容=stuff((SELECT ','+内容 from t where a.id=t.id for xml path('')),1,1,'')from t agroup by a.id/*id 内容----------------------1 a,e2 b3 a,c4 b,e5 a,b,c6 a,b,d,e7 b,d*/