34,590
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[A表]
if object_id('[A表]') is not null
drop table [A表]
create table [A表](
[ID] int,
[Tag] varchar(5)
)
go
insert [A表]
select 1,'1,2,3' union all
select 2,'1,3' union all
select 3,'2,3'
go
--> 测试数据:[B表]
if object_id('[B表]') is not null
drop table [B表]
create table [B表](
[ID] int,
[描述] varchar(6)
)
go
insert [B表]
select 1,'拉面' union all
select 2,'方便面' union all
select 3,'牛肉面'
go
;with t
as(
select a.*,b.描述
from [A表] a,[B表] b
where CHARINDEX(LTRIM(b.[ID]),a.Tag)>0
)
SELECT *FROM (SELECT DISTINCT Id,Tag FROM t)A
OUTER APPLY(
SELECT [描述]= STUFF(REPLACE(REPLACE(
( SELECT [描述] FROM t N
WHERE id = A.id and Tag=a.Tag
FOR XML AUTO
), '<b 描述="', ','), '"/>', ''), 1, 1, '')
)N
/*
Id Tag 描述
---------------------------------
1 1,2,3 拉面,方便面,牛肉面
2 1,3 拉面,牛肉面
3 2,3 方便面,牛肉面
*/
--A表(记录表)
DECLARE @A TABLE(id INT,Tag VARCHAR(10))
--ID Tag
-----------
INSERT INTO @A
SELECT 1, '1,2,3' UNION ALL
SELECT 2, '1,3' UNION ALL
SELECT 3, '2,3'
DECLARE @B TABLE(id INT,描述 VARCHAR(10))
-------------
INSERT INTO @B
SELECT 1, '拉面' UNION ALL
SELECT 2, '方便面' UNION ALL
SELECT 3, '牛肉面'
SELECT a.id,
a.Tag,
TagName=STUFF((SELECT ','+描述 FROM @B WHERE CHARINDEX(','+CAST(id AS VARCHAR(4))+',',','+a.Tag+',' )>0 FOR XML PATH('')),1,1,'')
FROM @A a
GROUP BY a.id,Tag
/*
id Tag TagName
----------- ---------- -------------------------
1 1,2,3 拉面,方便面,牛肉面
2 1,3 拉面,牛肉面
3 2,3 方便面,牛肉面
(3 行受影响)
*/