现有一个表 a :
col1 col2 col3
1 a null
2 b null
和一个表b:
col1 col2
1 ab
1 cd
2 b
2 dd
其中,b有中的col1的外键为表a的列 col1
现在求一个update语句,更新表a
使表a更新后的内容为:
col1 col2 col3
1 a ab,cd
2 b b,dd
即:根据表b的col2的字符串,拼接生成表a的col3,这条update语句应当怎么写?
col1 col2 col3
1 a null
2 b null
和一个表b:
col1 col2
1 ab
1 cd
2 b
2 dd
其中,b有中的col1的外键为表a的列 col1
现在求一个update语句,更新表a
使表a更新后的内容为:
col1 col2 col3
1 a ab,cd
2 b b,dd
即:根据表b的col2的字符串,拼接生成表a的col3,这条update语句应当怎么写?
--> 测试数据: [a]
if object_id('[a]') is not null drop table [a]
create table [a] (col1 int,col2 varchar(1),col3 sql_variant)
insert into [a]
select 1,'a',null union all
select 2,'b',null
--> 测试数据: [b]
if object_id('[b]') is not null drop table [b]
create table [b] (col1 int,col2 varchar(2))
insert into [b]
select 1,'ab' union all
select 1,'cd' union all
select 2,'b' union all
select 2,'dd'
go
--创建合并函数
create function gg(@col1 int)
returns varchar(100)
as
begin
declare @sql varchar(100)
select @sql=isnull(@sql+',','')+col2 from b where col1=@col1
return @sql
end
go
--修改
update a set col3=dbo.gg(col1) from a
go
select * from [a]
--先整一个函数
CREATE function F_GET_KEYWORD_STRING(@ARID varchar(50))
returns varchar(1000)
as
begin
declare @s varchar(1000)
select @s = isnull(@s,'')+a.cKeyword+' ' from dbo.BLOG_KEYWORD a,dbo.BLOG_AK b where a.cRID = b.cKRID and b.cARID = @ARID order by b.cOrder
return @s
end--给cKeywordString赋值
set @sqlString = ''
set @sqlString = @sqlString + ' update #tmpResult'
set @sqlString = @sqlString + ' set cKeywordString = isNull(dbo.F_GET_KEYWORD_STRING(cRID),'''')'
set @sqlString = @sqlString + ' where cID in(select top ' + convert(varchar(5),@pLimit) + ' cID from #tmpResult where cID > ' + convert(varchar(5),@pStart)+ 'order by cID)'
exec (@sqlString)
create table [a] (col1 int,col2 varchar(1),col3 varchar(10))
insert into [a]
select 1,'a',null union all
select 2,'b',null
--> 测试数据: [b]
if object_id('[b]') is not null drop table [b]
create table [b] (col1 int,col2 varchar(2))
insert into [b]
select 1,'ab' union all
select 1,'cd' union all
select 2,'b' union all
select 2,'dd'
gocreate function dbo.f_str(@col1 int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(col2 as varchar) from b where col1 = @col1
set @str = right(@str , len(@str) - 1)
return @str
end
go--调用函数
update a
set col3 = t.col2
from a,
(select col1 , col2 = dbo.f_str(col1) from b group by col1) t
where a.col1 = t.col1select * from adrop function dbo.f_strdrop table a , b/*
col1 col2 col3
----------- ---- ----------
1 a ab,cd
2 b b,dd(所影响的行数为 2 行)*/
insert into [a]
select 1,'a',null union all
select 2,'b',null
create table [b] (col1 int,col2 varchar(2))
insert into [b]
select 1,'ab' union all
select 1,'cd' union all
select 2,'b' union all
select 2,'dd'
goupdate a
set col3 = t.col2
from a,
(
select col1, [col2] = stuff((select ',' + [col2] from b t where col1 = b.col1 for xml path('')) , 1 , 1 , '')
from b
group by col1
) t
where a.col1 = t.col1select * from a
drop table a , b/*
col1 col2 col3
----------- ---- ----------
1 a ab,cd
2 b b,dd(2 行受影响)
*/