34,590
社区成员
发帖
与我相关
我的任务
分享
select b.*,stuff((select ','+a.name from a where charindex(','+code+',',','+b.code+',')>0 for xml path('')),1,1,'')codecn from b
drop table ta,tb
go
create table ta(code varchar(10),name varchar(10))
insert into ta values('01,02', 'A公司')
insert into ta values('02,03,01', 'B公司')
create table tb(code varchar(10),linkname varchar(10))
insert into tb values('01', 'aaa')
insert into tb values('02', 'bbb')
insert into tb values('03', 'ccc')
go
SELECT * FROM ta m
CROSS APPLY
(SELECT
STUFF((SELECT ',' + linkname AS [text()]
FROM tb n
WHERE ',' + m.code + ',' like '%,' + n.code + ',%'
FOR XML PATH('')), 1, 1, '') as linkname
) b
select code,name, lname=stuff((select ','+linkname from tb b where ',' + a.code + ',' like '%,' + b.code + ',%' for xml path('')), 1, 1, '')
from ta a
group by code,name
create table ta(code varchar(10),name varchar(10))
insert into ta values('01', 'A公司')
insert into ta values('02', 'B公司')
create table tb(code varchar(10),linkname varchar(10))
insert into tb values('01', 'aaa')
insert into tb values('01', 'bbb')
insert into tb values('01', 'ccc')
insert into tb values('02', 'ddd')
insert into tb values('02', 'eee')
go
select a.code,a.name,b.linkname
from ta a inner join
(SELECT distinct code,
STUFF((SELECT ',' + linkname AS [text()]
FROM tb n
WHERE n.code = m.code
FOR XML PATH('')), 1, 1, '') as linkname
FROM tb m
) b ON b.code=a.code
/*
code name linkname
---------- ---------- --------------
01 A公司 aaa bbb ccc
02 B公司 ddd eee
(2 行受影响)
*/