create table test1(id_no int,content varchar(100)) create table test2(id_no int,content varchar(100)) insert into test1 values(1,'a') insert into test1 values(1,'b') insert into test1 values(1,'c') insert into test1 values(1,'d') insert into test1 values(1,'e') insert into test1 values(1,'f') insert into test1 values(2,'A') insert into test1 values(2,'B') insert into test1 values(2,'C') insert into test1 values(2,'D') insert into test1 values(2,'E') insert into test1 values(2,'F') insert into test2 values(1,'0') insert into test2 values(2,'0') gocreate function getstr0(@id_no int) returns varchar(100) as begin declare @s varchar(100) select @s=isnull(@s,'')+[content] from test1 where id_no=@id_no return @s end goupdate test2 set [content]=[content]+dbo.getstr0(id_no)select * from test2 /* id_no content ----------- ---------------------------------------------------------------------------------------------------- 1 0abcdef 2 0ABCDEF(2 行受影响) */drop function dbo.getstr0 drop table test1,test2
--本贴说明 -->创建测试数据 create table test1(id_no int,content varchar(100)) create table test2(id_no int,content varchar(100)) insert into test1 values(1,'a') insert into test1 values(1,'b') insert into test1 values(1,'c') insert into test1 values(1,'d') insert into test1 values(1,'e') insert into test1 values(1,'f') insert into test1 values(2,'A') insert into test1 values(2,'B') insert into test1 values(2,'C') insert into test1 values(2,'D') insert into test1 values(2,'E') insert into test1 values(2,'F') insert into test2 values(1,'0') insert into test2 values(2,'0') go -->创建自定义函数 create function getstr0(@id_no int) returns varchar(100) as begin declare @s varchar(100) select @s=isnull(@s,'')+[content] from test1 where id_no=@id_no return @s end go -->开始更新 update test2 set [content]=[content]+dbo.getstr0(id_no) -->查询结果 select * from test2 /* id_no content ----------- ---------------------------------------------------------------------------------------------------- 1 0abcdef 2 0ABCDEF(2 行受影响) */ -->删除测试环境 drop function dbo.getstr0 drop table test1,test2
是合并字符吧?if object_id('tb') is not null drop table tb create table tb ( col1 varchar(10), col2 int ) insert into tb select 'a',1 union all select 'a',2 union all select 'b',1 union all select 'b',2 union all select 'b',3 select col1,col2=cast(col2 as varchar(100)) into #t1 from tb order by col1,col2 declare @col1 varchar(20) declare @col2 varchar(100) update #t1 set @col2= case when @col1=col1 then @col2+ ',' +col2 else col2 end, @col1=col1, col2=@col2 select col1,col2=max(col2) from #t1 group by col1col1
create table test1(id_no int,content varchar(100)) create table test2(id_no int,content varchar(100)) insert into test1 values(1,'a') insert into test1 values(1,'b') insert into test1 values(1,'c') insert into test1 values(1,'d') insert into test1 values(1,'e') insert into test1 values(1,'f') insert into test1 values(2,'A') insert into test1 values(2,'B') insert into test1 values(2,'C') insert into test1 values(2,'D') insert into test1 values(2,'E') insert into test1 values(2,'F') insert into test2 values(1,'0') insert into test2 values(2,'0') update test2 set [content] = [content] + c.c from test2 a inner join ( select id_no, c =stuff((select [content] + '' from test1 where id_no = b.id_no for xml path('')),1,1,'') from test1 b group by id_no )c on c.id_no = a.id_noselect * from test2id_no content ----------- ---------------------------------------------------------------------------------------------------- 1 0bcdef 2 0BCDEF(2 row(s) affected)
create table test1(id_no int,content varchar(100)) create table test2(id_no int,content varchar(100)) insert into test1 values(1,'a') insert into test1 values(1,'b') insert into test1 values(1,'c') insert into test1 values(1,'d') insert into test1 values(1,'e') insert into test1 values(1,'f') insert into test1 values(2,'A') insert into test1 values(2,'B') insert into test1 values(2,'C') insert into test1 values(2,'D') insert into test1 values(2,'E') insert into test1 values(2,'F') insert into test2 values(1,'0') insert into test2 values(2,'0') update test2 set [content] = [content] + c.c from test2 a inner join ( select id_no, c =stuff((select [content] + '' from test1 where id_no = b.id_no for xml path('')),1,1,'') from test1 b group by id_no )c on c.id_no = a.id_noselect * from test2id_no content ----------- ---------------------------------------------------------------------------------------------------- 1 0bcdef 2 0BCDEF(2 row(s) affected)
create table test1(id_no int,content varchar(100)) create table test2(id_no int,content varchar(100)) insert into test1 values(1,'a') insert into test1 values(1,'b') insert into test1 values(1,'c') insert into test1 values(1,'d') insert into test1 values(1,'e') insert into test1 values(1,'f') insert into test1 values(2,'A') insert into test1 values(2,'B') insert into test1 values(2,'C') insert into test1 values(2,'D') insert into test1 values(2,'E') insert into test1 values(2,'F') insert into test2 values(1,'0') insert into test2 values(2,'0') select id_no,content=cast(content as varchar(100)) into #t1 from test1 order by id_no,content declare @col1 varchar(20) declare @col2 varchar(100) update #t1 set @col2= case when @col1=id_no then @col2+ '' +content else content end, @col1=id_no, content=@col2 select id_no,content=max(content) from #t1 group by id_no update a set a.content=b.content from test2 a,#t1 b where a.id_no=b.id_noid_no content 1 abcdef 2 ABCDEF
--2005? 那就方便多了create table test1(id_no int,content varchar(100)) create table test2(id_no int,content varchar(100)) insert into test1 values(1,'a') insert into test1 values(1,'b') insert into test1 values(1,'c') insert into test1 values(1,'d') insert into test1 values(1,'e') insert into test1 values(1,'f') insert into test1 values(2,'A') insert into test1 values(2,'B') insert into test1 values(2,'C') insert into test1 values(2,'D') insert into test1 values(2,'E') insert into test1 values(2,'F') insert into test2 values(1,'0') insert into test2 values(2,'0') GOUpdate Test2 set [content]=Test2.[content]+T2.[content] from Test2, (SELECT * FROM( SELECT DISTINCT id_no FROM test1 )A OUTER APPLY( SELECT [content]= REPLACE(REPLACE( ( SELECT content FROM test1 N WHERE id_no = A.id_no FOR XML AUTO ), '<N content="', ''), '"/>', '') )N ) T2 where Test2.id_no=T2.id_no GOselect * from test2 /* 1 0abcdef 2 0ABCDEF */drop table test1,test2GO--function 更新 create table test1(id_no int,content varchar(100)) create table test2(id_no int,content varchar(100)) insert into test1 values(1,'a') insert into test1 values(1,'b') insert into test1 values(1,'c') insert into test1 values(1,'d') insert into test1 values(1,'e') insert into test1 values(1,'f') insert into test1 values(2,'A') insert into test1 values(2,'B') insert into test1 values(2,'C') insert into test1 values(2,'D') insert into test1 values(2,'E') insert into test1 values(2,'F') insert into test2 values(1,'0') insert into test2 values(2,'0') GOCreate Function dbo.fn_test (@id_no int) returns varchar(8000) AS begin declare @re varchar(8000) set @re='' select @re=@re+content from test1 where id_no=@id_no return @reend GOUpdate test2 set content=content+dbo.fn_test(id_no) Goselect * from test2 /* 1 0abcdef 2 0ABCDEF*/ GOdrop table test1,test2 drop function dbo.fn_testGO--臨時表更新create table test1(id_no int,content varchar(100)) create table test2(id_no int,content varchar(100)) insert into test1 values(1,'a') insert into test1 values(1,'b') insert into test1 values(1,'c') insert into test1 values(1,'d') insert into test1 values(1,'e') insert into test1 values(1,'f') insert into test1 values(2,'A') insert into test1 values(2,'B') insert into test1 values(2,'C') insert into test1 values(2,'D') insert into test1 values(2,'E') insert into test1 values(2,'F') insert into test2 values(1,'0') insert into test2 values(2,'0') GOCreate proc dbo.usp_test ASselect id_no, convert(varchar(8000),content) as content into #t from test1 order by id_nodeclare @id_no int, @content varchar(8000) Update #t set @content=case when id_no=@id_no then @content+content else content end, @id_no=id_no, content=@contentUpdate test2 set content=A.content+B.content from test2 A, (select t1.id_no, t1.content from #t t1 inner join (select id_no, max(len(content)) as max_content from #t group by id_no ) t2 on t1.id_no=t2.id_no and len(t1.content)=t2.max_content) B where A.id_no=B.id_noGOexec dbo.usp_test GOselect * from test2 /* 1 0abcdef 2 0ABCDEF*/ GOdrop table test1, test2 drop proc usp_testGO
create function getstr0(@id_no int) returns varchar(100) as begin declare @s varchar(100) select @s=isnull(@s,'')+[content] from test1 where id_no=@id_no return @s end go
[code=SQL]create table test1(id_no int,content varchar(100)) create table test2(id_no int,content varchar(100)) insert into test1 values(1,'a') insert into test1 values(1,'b') insert into test1 values(1,'c') insert into test1 values(1,'d') insert into test1 values(1,'e') insert into test1 values(1,'f') insert into test1 values(2,'A') insert into test1 values(2,'B') insert into test1 values(2,'C') insert into test1 values(2,'D') insert into test1 values(2,'E') insert into test1 values(2,'F') insert into test2 values(1,'0') insert into test2 values(2,'0') go create function dbo.FC_Str(@id_no int) returns varchar(1000) as begin declare @i varchar(100) set @i='' select @i=@i+','+content from test1 where id_no=@id_no return stuff(@i,1,1,'') end update test2 set content=T2.content+','+T1.CC from test2 T2,(select distinct(id_no),dbo.FC_Str(id_no) as CC from test1) T1 where T1.id_no=T2.id_noselect * from test2id_no content ----------- ---------------------------------------------------------------------------------------------------- 1 0,a,b,c,d,e,f 2 0,A,B,C,D,E,F(2 行受影响)[/code]
id_no为2的content都变成'0ABCDEF'是这个要求吗?
create table test1(id_no int,content varchar(100))
create table test2(id_no int,content varchar(100))
insert into test1 values(1,'a')
insert into test1 values(1,'b')
insert into test1 values(1,'c')
insert into test1 values(1,'d')
insert into test1 values(1,'e')
insert into test1 values(1,'f')
insert into test1 values(2,'A')
insert into test1 values(2,'B')
insert into test1 values(2,'C')
insert into test1 values(2,'D')
insert into test1 values(2,'E')
insert into test1 values(2,'F') insert into test2 values(1,'0')
insert into test2 values(2,'0')
gocreate function getstr0(@id_no int)
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s,'')+[content] from test1 where id_no=@id_no
return @s
end
goupdate test2 set [content]=[content]+dbo.getstr0(id_no)select * from test2
/*
id_no content
----------- ----------------------------------------------------------------------------------------------------
1 0abcdef
2 0ABCDEF(2 行受影响)
*/drop function dbo.getstr0
drop table test1,test2
-->创建测试数据
create table test1(id_no int,content varchar(100))
create table test2(id_no int,content varchar(100))
insert into test1 values(1,'a')
insert into test1 values(1,'b')
insert into test1 values(1,'c')
insert into test1 values(1,'d')
insert into test1 values(1,'e')
insert into test1 values(1,'f')
insert into test1 values(2,'A')
insert into test1 values(2,'B')
insert into test1 values(2,'C')
insert into test1 values(2,'D')
insert into test1 values(2,'E')
insert into test1 values(2,'F') insert into test2 values(1,'0')
insert into test2 values(2,'0')
go
-->创建自定义函数
create function getstr0(@id_no int)
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s,'')+[content] from test1 where id_no=@id_no
return @s
end
go
-->开始更新
update test2 set [content]=[content]+dbo.getstr0(id_no)
-->查询结果
select * from test2
/*
id_no content
----------- ----------------------------------------------------------------------------------------------------
1 0abcdef
2 0ABCDEF(2 行受影响)
*/
-->删除测试环境
drop function dbo.getstr0
drop table test1,test2
create table tb
(
col1 varchar(10),
col2 int
)
insert into tb select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'b',3 select col1,col2=cast(col2 as varchar(100)) into #t1 from tb order by col1,col2
declare @col1 varchar(20)
declare @col2 varchar(100)
update #t1 set @col2=
case when @col1=col1 then @col2+ ',' +col2
else col2 end,
@col1=col1,
col2=@col2 select col1,col2=max(col2) from #t1 group by col1col1
不知道我说清楚没有
create table test2(id_no int,content varchar(100))
insert into test1 values(1,'a')
insert into test1 values(1,'b')
insert into test1 values(1,'c')
insert into test1 values(1,'d')
insert into test1 values(1,'e')
insert into test1 values(1,'f')
insert into test1 values(2,'A')
insert into test1 values(2,'B')
insert into test1 values(2,'C')
insert into test1 values(2,'D')
insert into test1 values(2,'E')
insert into test1 values(2,'F') insert into test2 values(1,'0')
insert into test2 values(2,'0') update test2
set [content] = [content] + c.c
from test2 a
inner join
(
select id_no,
c =stuff((select [content] + '' from test1 where id_no = b.id_no for xml path('')),1,1,'')
from test1 b group by id_no
)c
on c.id_no = a.id_noselect * from test2id_no content
----------- ----------------------------------------------------------------------------------------------------
1 0bcdef
2 0BCDEF(2 row(s) affected)
create table test1(id_no int,content varchar(100))
create table test2(id_no int,content varchar(100))
insert into test1 values(1,'a')
insert into test1 values(1,'b')
insert into test1 values(1,'c')
insert into test1 values(1,'d')
insert into test1 values(1,'e')
insert into test1 values(1,'f')
insert into test1 values(2,'A')
insert into test1 values(2,'B')
insert into test1 values(2,'C')
insert into test1 values(2,'D')
insert into test1 values(2,'E')
insert into test1 values(2,'F') insert into test2 values(1,'0')
insert into test2 values(2,'0') update test2
set [content] = [content] + c.c
from test2 a
inner join
(
select id_no,
c =stuff((select [content] + '' from test1 where id_no = b.id_no for xml path('')),1,1,'')
from test1 b group by id_no
)c
on c.id_no = a.id_noselect * from test2id_no content
----------- ----------------------------------------------------------------------------------------------------
1 0bcdef
2 0BCDEF(2 row(s) affected)
create table test1(id_no int,content varchar(100))
create table test2(id_no int,content varchar(100))
insert into test1 values(1,'a')
insert into test1 values(1,'b')
insert into test1 values(1,'c')
insert into test1 values(1,'d')
insert into test1 values(1,'e')
insert into test1 values(1,'f')
insert into test1 values(2,'A')
insert into test1 values(2,'B')
insert into test1 values(2,'C')
insert into test1 values(2,'D')
insert into test1 values(2,'E')
insert into test1 values(2,'F') insert into test2 values(1,'0')
insert into test2 values(2,'0') select id_no,content=cast(content as varchar(100)) into #t1 from test1 order by id_no,content
declare @col1 varchar(20)
declare @col2 varchar(100)
update #t1 set @col2=
case when @col1=id_no then @col2+ '' +content
else content end,
@col1=id_no,
content=@col2 select id_no,content=max(content) from #t1 group by id_no
update a set a.content=b.content from test2 a,#t1 b where a.id_no=b.id_noid_no content
1 abcdef
2 ABCDEF
--2005? 那就方便多了create table test1(id_no int,content varchar(100))
create table test2(id_no int,content varchar(100))
insert into test1 values(1,'a')
insert into test1 values(1,'b')
insert into test1 values(1,'c')
insert into test1 values(1,'d')
insert into test1 values(1,'e')
insert into test1 values(1,'f')
insert into test1 values(2,'A')
insert into test1 values(2,'B')
insert into test1 values(2,'C')
insert into test1 values(2,'D')
insert into test1 values(2,'E')
insert into test1 values(2,'F') insert into test2 values(1,'0')
insert into test2 values(2,'0')
GOUpdate Test2
set [content]=Test2.[content]+T2.[content]
from Test2,
(SELECT *
FROM(
SELECT DISTINCT
id_no
FROM test1
)A
OUTER APPLY(
SELECT [content]= REPLACE(REPLACE(
(
SELECT content FROM test1 N
WHERE id_no = A.id_no
FOR XML AUTO
), '<N content="', ''), '"/>', '')
)N ) T2
where Test2.id_no=T2.id_no
GOselect * from test2
/*
1 0abcdef
2 0ABCDEF
*/drop table test1,test2GO--function 更新
create table test1(id_no int,content varchar(100))
create table test2(id_no int,content varchar(100))
insert into test1 values(1,'a')
insert into test1 values(1,'b')
insert into test1 values(1,'c')
insert into test1 values(1,'d')
insert into test1 values(1,'e')
insert into test1 values(1,'f')
insert into test1 values(2,'A')
insert into test1 values(2,'B')
insert into test1 values(2,'C')
insert into test1 values(2,'D')
insert into test1 values(2,'E')
insert into test1 values(2,'F') insert into test2 values(1,'0')
insert into test2 values(2,'0')
GOCreate Function dbo.fn_test (@id_no int)
returns varchar(8000)
AS
begin
declare @re varchar(8000)
set @re=''
select @re=@re+content from test1 where id_no=@id_no
return @reend
GOUpdate test2 set content=content+dbo.fn_test(id_no)
Goselect * from test2
/*
1 0abcdef
2 0ABCDEF*/
GOdrop table test1,test2
drop function dbo.fn_testGO--臨時表更新create table test1(id_no int,content varchar(100))
create table test2(id_no int,content varchar(100))
insert into test1 values(1,'a')
insert into test1 values(1,'b')
insert into test1 values(1,'c')
insert into test1 values(1,'d')
insert into test1 values(1,'e')
insert into test1 values(1,'f')
insert into test1 values(2,'A')
insert into test1 values(2,'B')
insert into test1 values(2,'C')
insert into test1 values(2,'D')
insert into test1 values(2,'E')
insert into test1 values(2,'F') insert into test2 values(1,'0')
insert into test2 values(2,'0') GOCreate proc dbo.usp_test
ASselect id_no, convert(varchar(8000),content) as content
into #t
from test1
order by id_nodeclare @id_no int, @content varchar(8000)
Update #t
set @content=case when id_no=@id_no
then @content+content
else content
end,
@id_no=id_no,
content=@contentUpdate test2
set content=A.content+B.content
from test2 A,
(select t1.id_no, t1.content
from #t t1
inner join (select id_no, max(len(content)) as max_content from #t group by id_no ) t2
on t1.id_no=t2.id_no
and len(t1.content)=t2.max_content) B
where A.id_no=B.id_noGOexec dbo.usp_test
GOselect * from test2
/*
1 0abcdef
2 0ABCDEF*/
GOdrop table test1, test2
drop proc usp_testGO
create function getstr0(@id_no int)
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s,'')+[content] from test1 where id_no=@id_no
return @s
end
go
create table test2(id_no int,content varchar(100))
insert into test1 values(1,'a')
insert into test1 values(1,'b')
insert into test1 values(1,'c')
insert into test1 values(1,'d')
insert into test1 values(1,'e')
insert into test1 values(1,'f')
insert into test1 values(2,'A')
insert into test1 values(2,'B')
insert into test1 values(2,'C')
insert into test1 values(2,'D')
insert into test1 values(2,'E')
insert into test1 values(2,'F') insert into test2 values(1,'0')
insert into test2 values(2,'0')
go
create function dbo.FC_Str(@id_no int)
returns varchar(1000)
as
begin
declare @i varchar(100)
set @i=''
select @i=@i+','+content from test1 where id_no=@id_no
return stuff(@i,1,1,'')
end
update test2 set content=T2.content+','+T1.CC from test2 T2,(select distinct(id_no),dbo.FC_Str(id_no) as CC from test1) T1 where T1.id_no=T2.id_noselect * from test2id_no content
----------- ----------------------------------------------------------------------------------------------------
1 0,a,b,c,d,e,f
2 0,A,B,C,D,E,F(2 行受影响)[/code]