create function f_str(@f3 int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+f4 from B where f3=@f3
if @str is null
return '' return @str
end
go--更新
update A
set f2=dbo.f_str(f1)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+f4 from B where f3=@f3
if @str is null
return '' return @str
end
go--更新
update A
set f2=dbo.f_str(f1)
(select distinct m.f3 as f1,m.f4 as f2,n.f3,n.f4 from b m,b n where m.f3=n.f3) as t
where t.f1=t.f3 and t.f2<t.f4
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select distinct @str=@str+f4 from B where f3=@f3
if @str is null
return '' return @str
end
go--更新
update A
set f2=dbo.f_str(f1)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+f4 from (select f3,f4 from B group by f3,f4) t where f3=@f3
if @str is null
return '' return @str
end
go--更新
update A
set f2=dbo.f_str(f1)
from (select f1,f2+f4 as f2 from
(select distinct m.f3 as f1,m.f4 as f2,n.f3,n.f4 from b m,b n where m.f3=n.f3) as t
where t.f1=t.f3 and t.f2<t.f4) tselect * from a
(所影响的行数为 2 行)f1 f2
----------- ----
1 ab
2 eg(所影响的行数为 2 行)建议使用上面vivianfdlpw()的通用格式,我这种是应对通常格式的,对于出现特定的情况无能为力.:)
go
create table b(f3 int,f4 varchar(10))
go
insert into a(f1)
select 1 union select 2
go
insert into b
select 1,'a' union all
select 1,'a' union all
select 1,'b' union all
select 2,'e' union all
select 2,'g'
gocreate function f_a(@f1 int)
returns varchar(3000)
as
begin
declare @str varchar(3000)
set @str=''
select @str=@str+ f4 from b where f3=@f1 group by f3,f4
return @str
end
goupdate a set f2=dbo.f_a(f1)
select * from a
/*
f1 f2
----------- ----------
1 ab
2 eg(所影响的行数为 2 行)
*/