if not object_id('t1') is null drop table t1 Go Create table t1([CDS] nvarchar(5)) Insert t1 select N'1,2,3' union all select N'1,3' Go if not object_id('t2') is null drop table t2 Go Create table t2([CD] int,[Name] nvarchar(1)) Insert t2 select 1,N'孙' union all select 2,N'李' union all select 3,N'王' Go select [CDS], stuff((select ','+[Name] from t2 where charindex(','+ltrim([CD] )+',',','+t.[CDS] +',')>0 for xml path('')),1,1,'')Name from t1 t
create table CTB ( IDS varchar(20) ) gocreate table CTBS ( ID varchar(20), name varchar(10) ) insert into CTB values('1,2,3') insert into CTB values('1,2')insert into CTBS values('1','孙') insert into CTBS values('2','李') insert into CTBS values('3','王') cREATE TABLE #TMP ( id INT IDENTITY(1,1), IDs varchar(20), name varchar(10) ) INSERT INTO #TMP SELECT IDS,IDS AS NAME FROM CTB declare @tr int declare @cnt int set @cnt=1 set @tr=1 while (@tr=1) begin WHILE EXISTS(SELECT TOP 1 * FROM #TMP as a ,CTBS as b WHERE a.NAME like '%'+b.ID +'%' AND A.id=@cnt) BEGIN UPDATE A SET A.name=REPLACE(A.name,B.ID,b.name) from #TMP AS A, CTBS AS B where a.NAME like '%'+b.ID +'%' AND A.id=@cnt END SET @cnt=@cnt+1 IF (@cnt>(SELECT COUNT(idS) FROM CTB)) SET @tr=2 end
补充4楼的,最后 select * from #tmp
三楼 兄弟强大,像 stuff ,for xml path 这样的我还是第一见,学习了 我原来想用个函数来着
drop table t1
Go
Create table t1([CDS] nvarchar(5))
Insert t1
select N'1,2,3' union all
select N'1,3'
Go
if not object_id('t2') is null
drop table t2
Go
Create table t2([CD] int,[Name] nvarchar(1))
Insert t2
select 1,N'孙' union all
select 2,N'李' union all
select 3,N'王'
Go
select [CDS],
stuff((select ','+[Name]
from t2
where charindex(','+ltrim([CD] )+',',','+t.[CDS] +',')>0
for xml path('')),1,1,'')Name
from t1 t
create table CTB
(
IDS varchar(20)
)
gocreate table CTBS
(
ID varchar(20),
name varchar(10)
)
insert into CTB values('1,2,3')
insert into CTB values('1,2')insert into CTBS values('1','孙')
insert into CTBS values('2','李')
insert into CTBS values('3','王')
cREATE TABLE #TMP
(
id INT IDENTITY(1,1),
IDs varchar(20),
name varchar(10)
)
INSERT INTO #TMP SELECT IDS,IDS AS NAME FROM CTB
declare @tr int
declare @cnt int
set @cnt=1
set @tr=1
while (@tr=1)
begin
WHILE EXISTS(SELECT TOP 1 * FROM #TMP as a ,CTBS as b WHERE a.NAME like '%'+b.ID +'%' AND A.id=@cnt)
BEGIN
UPDATE A SET A.name=REPLACE(A.name,B.ID,b.name) from #TMP AS A, CTBS AS B where a.NAME like '%'+b.ID +'%' AND A.id=@cnt
END
SET @cnt=@cnt+1
IF (@cnt>(SELECT COUNT(idS) FROM CTB))
SET @tr=2
end
三楼 兄弟强大,像 stuff ,for xml path 这样的我还是第一见,学习了
我原来想用个函数来着