表 j_gjc
想实现的结果:wenzhangid相同、leixing相同的关键词,放到一列中,每个关键词之间以分号分隔,例如:
wenzhangid 为 18968的,最后结果应为 超流Fermi气体;非谐振势;集体激发
用sql函数实现
函数,将 j_gjc 表中的 关键词 写到 xhl11 表中
/* 定义 函数,将中文关键词累加到一列*/
create function [dbo].[f_get_gjc_cn](@uid varchar(50)) returns varchar(2000)
AS
begin
declare @r varchar(2000)
set @r=''
select @r=@r+(rtrim(guanjianci)+';') from j_gjc a where a.WenZhang_Id=@uid and a.LeiXing='CN'
order by WenZhang_Id,ID
while CHARINDEX(';;',@r)>0
begin
select @r=replace(@r,';;',';')
end
return @r
end
/* 定义 函数,将英文文关键词累加到一列*/
create function [dbo].[f_get_gjc_en](@uid varchar(50)) returns varchar(2000)
AS
begin
declare @r varchar(2000)
set @r=''
select @r=@r+(rtrim(guanjianci)+';') from j_gjc a where a.WenZhang_Id=@uid and a.LeiXing='EN'
order by WenZhang_Id,ID
while CHARINDEX(';;',@r)>0
begin
select @r=replace(@r,';;',';')
end
return @r
end
update xhl11 set
gjc=dbo.f_get_gjc_cn(a.id)
from xhl11 a
where dbo.f_get_gjc_cn(a.id)<>''
update xhl11 set
gjc_en=dbo.f_get_gjc_en(a.id)
from xhl11 a
where dbo.f_get_gjc_en(a.id)<>''