--sql2005投机方法: if object_id('[tb1]') is not null drop table [tb1] go create table [tb1]([aid] int,[aname] varchar(6)) insert [tb1] select 1,'1,2,3,' union all select 2,'1,2,' if object_id('[tb2]') is not null drop table [tb2] go create table [tb2]([bid] int,[bname] varchar(256)) insert [tb2] select 1,'你' union all select 2,'我' union all select 3,'他' go --select * from [tb1] --select * from [tb2]with szx as ( select aid,cast(aname as varchar(8000)) aname,bid=0 from tb1 union all select a.aid,stuff(replace(','+aname,','+rtrim(b.bid)+',',','+bname+','),1,1,''),b.bid from szx a join tb2 b on charindex(','+rtrim(b.bid)+',',','+aname)>0 and a.bid<b.bid ) select aid,aname from szx t where not exists(select 1 from szx where aid=t.aid and datalength(aname)>datalength(t.aname)) order by aid /* aid aname ----------- ------------- 1 你,我,他, 2 你,我,(2 行受影响) */
declare @b varchar(10),@c varchar(1),@length int declare @name varchar(20),@sql varchar(800) create table #T(id int,name nvarchar(20)) set @name='' declare @count int set @count=1 while @count<=convert(int,(select count(*) from tb1)) begin set @length=1 while @length<=len(@b) begin set @c=substring(@b,@length,1) set @name=@name+','+(select bname from tb2 where bid=@c) if @length=len(@b) begin set @sql= 'insert into #T values('+convert(varchar,@count)+','''+right(@name,len( @name)-1)+','')' exec(@sql) end set @length=@length+1 end set @name='' set @count=@count+1 end select * from #T drop table #T简单快速...
declare @b varchar(10),@c varchar(1),@length int 定义什么
复制漏掉了declare @b varchar(10),@c varchar(1),@length int declare @name varchar(20),@sql varchar(800) create table #T(id int,name nvarchar(20)) set @name='' declare @count int set @count=1 while @count<=convert(int,(select count(*) from tb1)) begin set @b=(select replace(aname,',','') from tb1 where aid=@count) --每行找出来的存于临时字符串 set @length=1 while @length<=len(@b) begin set @c=substring(@b,@length,1) set @name=@name+','+(select bname from tb2 where bid=@c) if @length=len(@b) begin set @sql= 'insert into #T values('+convert(varchar,@count)+','''+right(@name,len( @name)-1)+','')' exec(@sql) end set @length=@length+1 end set @name='' set @count=@count+1 end select * from #T drop table #T
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([aid] int,[aname] varchar(6))
insert [tb1]
select 1,'1,2,3,' union all
select 2,'1,2,'
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([bid] int,[bname] varchar(256))
insert [tb2]
select 1,'你' union all
select 2,'我' union all
select 3,'他'
go
--select * from [tb1]
--select * from [tb2]with szx as
(
select aid,cast(aname as varchar(8000)) aname,bid=0 from tb1
union all
select a.aid,stuff(replace(','+aname,','+rtrim(b.bid)+',',','+bname+','),1,1,''),b.bid
from szx a join tb2 b
on charindex(','+rtrim(b.bid)+',',','+aname)>0 and a.bid<b.bid
)
select aid,aname from szx t
where not exists(select 1 from szx where aid=t.aid and datalength(aname)>datalength(t.aname))
order by aid
/*
aid aname
----------- -------------
1 你,我,他,
2 你,我,(2 行受影响)
*/
declare @b varchar(10),@c varchar(1),@length int
declare @name varchar(20),@sql varchar(800)
create table #T(id int,name nvarchar(20))
set @name=''
declare @count int
set @count=1
while @count<=convert(int,(select count(*) from tb1))
begin
set @length=1
while @length<=len(@b)
begin
set @c=substring(@b,@length,1)
set @name=@name+','+(select bname from tb2 where bid=@c)
if @length=len(@b)
begin
set @sql= 'insert into #T values('+convert(varchar,@count)+','''+right(@name,len( @name)-1)+','')'
exec(@sql)
end
set @length=@length+1
end
set @name=''
set @count=@count+1
end
select * from #T
drop table #T简单快速...
定义什么
复制漏掉了declare @b varchar(10),@c varchar(1),@length int
declare @name varchar(20),@sql varchar(800)
create table #T(id int,name nvarchar(20))
set @name=''
declare @count int
set @count=1
while @count<=convert(int,(select count(*) from tb1))
begin
set @b=(select replace(aname,',','') from tb1 where aid=@count) --每行找出来的存于临时字符串
set @length=1
while @length<=len(@b)
begin
set @c=substring(@b,@length,1)
set @name=@name+','+(select bname from tb2 where bid=@c)
if @length=len(@b)
begin
set @sql= 'insert into #T values('+convert(varchar,@count)+','''+right(@name,len( @name)-1)+','')'
exec(@sql)
end
set @length=@length+1
end
set @name=''
set @count=@count+1
end
select * from #T
drop table #T