求救一个update语句，散分感谢

[rainvictor]

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 ，使得为(1,'0abcdef')(2,'0ABCDEF')
注：不要一条一条做，也不要用循环或者游标，因为是大量数据（test1几十万条记录,test2上千条记录），会很慢。
各位有没有什么其他的办法？
最好是一条语句就能更新掉，而且要保证效率。

[Zoezs]

[Quote=引用楼主 rainvictor 的帖子:]
求一个update语句，更新test2 ，使得为(1,'0abcdef')(2,'0ABCDEF')
注：不要一条一条做，也不要用循环或者游标，因为是大量数据（test1几十万条记录,test2上千条记录），会很慢。
各位有没有什么其他的办法？
最好是一条语句就能更新掉，而且要保证效率。
[/Quote]
你这样的要求直接update不就行了？

[Zoezs]

你的意思是要test2中id_no为1的content都变成'0abcdef',
id_no为2的content都变成'0ABCDEF'是这个要求吗？

[htl258_Tony]

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

[htl258_Tony]

--本贴说明
-->创建测试数据
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

[Zoezs]

是合并字符吧？

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 

[claro]

帮顶
没看懂

[rainvictor]

test2表中的记录不增减，只是test2.content会合并test2中所有id_no和test1中id_no一样的test1.content记录
不知道我说清楚没有

[yygyogfny]

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_no

select * from test2

id_no content
----------- ----------------------------------------------------------------------------------------------------
1 0bcdef
2 0BCDEF

(2 row(s) affected)

[yygyogfny]

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_no

select * from test2

id_no       content
----------- ----------------------------------------------------------------------------------------------------
1           0bcdef
2           0BCDEF

(2 row(s) affected)

[htl258_Tony]

[Quote=引用 7 楼 rainvictor 的回复:]
test2表中的记录不增减，只是test2.content会合并test2中所有id_no和test1中id_no一样的test1.content记录
不知道我说清楚没有
[/Quote]
3 4楼的看了没有

[yygyogfny]

[Quote=引用 7 楼 rainvictor 的回复:]
test2表中的记录不增减，只是test2.content会合并test2中所有id_no和test1中id_no一样的test1.content记录
不知道我说清楚没有
[/Quote]

是2005吗?

[Zoezs]

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_no

id_no    content
1    abcdef
2    ABCDEF

[playwarcraft]

--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') 
GO

Update 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
GO

select * from test2
/*
1    0abcdef
2    0ABCDEF
*/

drop table test1,test2

GO

--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') 
GO

Create 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 @re

end
GO

Update test2  set content=content+dbo.fn_test(id_no)
Go

select * from test2
/*
1    0abcdef
2    0ABCDEF

*/
GO

drop table test1,test2
drop function dbo.fn_test

GO

--臨時表更新

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 proc dbo.usp_test
AS

select   id_no, convert(varchar(8000),content) as content
into #t 
from test1
order by id_no

declare @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=@content

Update 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_no

GO

exec dbo.usp_test
GO

select * from test2
/*
1    0abcdef
2    0ABCDEF

*/
GO

drop table test1, test2
drop proc usp_test

GO

[lihan6415151528]

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

[ws_hgo]

等下
我先看下

[ws_hgo]

[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_no

select * from test2

id_no content
----------- ----------------------------------------------------------------------------------------------------
1 0,a,b,c,d,e,f
2 0,A,B,C,D,E,F

(2 行受影响)[/code]

[ws_hgo]

那个函数','去掉下
呵呵

[ws_hgo]

人齐结贴