怎样的返回?将函数内部查询语句的结果返回?把函数修改成如下: ---------------------------------------------------------------------------------------------------------------- CREATE function f_news_linknum(@ArticleID int) returns @r table(articleid int,title varchar(1000),classid int,Nkey varchar(200),dateandtime,[匹配度] int) begin declare @Tags varchar(200); set @Tags=(select nkey from article where articleid=@ArticleID)
insert into @r select top 5 articleid,title,classid,Nkey,dateandtime,count(1) from article a, dbo.GetRecords(@Tags) b where charindex(','+b.record+',',','+a.nkey+',')>0 and ArticleID<>@ArticleID and 匹配度=1 group by articleid,classid,title,nkey,dateandtime
return @r end go
我是打算这样写update article set link1=f_news_linknum(articleid,1) update article set link2=f_news_linknum(articleid,2) update article set link3=f_news_linknum(articleid,3) update article set link4=f_news_linknum(articleid,4) update article set link5=f_news_linknum(articleid,5) update article set link6=f_news_linknum(articleid,6) update article set link7=f_news_linknum(articleid,7) update article set link8=f_news_linknum(articleid,8) update article set link9=f_news_linknum(articleid,9) update article set link10=f_news_linknum(articleid,10) 另外不知道我这样做(建立10个字段,又用10个update语句是不是挺笨的) 另外这个函数还不会写呢。
哦,写错了 是要这样调用 update article set link1=f_news_linknum(1) update article set link2=f_news_linknum(2) update article set link3=f_news_linknum(3) update article set link4=f_news_linknum(4) update article set link5=f_news_linknum(5) update article set link6=f_news_linknum(6) update article set link7=f_news_linknum(7) update article set link8=f_news_linknum(8) update article set link9=f_news_linknum(9) update article set link10=f_news_linknum(10)
不对,还是上面的。 update article set link1=f_news_linknum(articleid,1) update article set link2=f_news_linknum(articleid,2) update article set link3=f_news_linknum(articleid,3) update article set link4=f_news_linknum(articleid,4) update article set link5=f_news_linknum(articleid,5) update article set link6=f_news_linknum(articleid,6) update article set link7=f_news_linknum(articleid,7) update article set link8=f_news_linknum(articleid,8) update article set link9=f_news_linknum(articleid,9) update article set link10=f_news_linknum(articleid,10) 另外不知道我这样做(建立10个字段,又用10个update语句是不是挺笨的) 另外这个函数还不会写呢。
link1(5,北京有3个,上海有2个,3+2=5),实际里面有一条记录包括了北京和上海,统计时计作2?不排除重复数据比较困难,否则,用这个: ------------------------------------------------------------------------- create table article(articleid int,nkey varchar(200)) insert into article select 1,'北京,上海' insert into article select 2,'山西,上海' insert into article select 3,'北京,陕西' insert into article select 4,'北京,大连' insert into article select 5,'北京,上海' go create function f_news_linknum(@ArticleID int,@num int) returns int as begin declare @t1 table(nkey varchar(200)) declare @t2 table(ArticleID int,nkey varchar(200)) declare @nkey varchar(200),@cnt int
select @nkey=nkey from article where articleid=@ArticleID
while (charindex(',',@nkey)>0) begin insert into @t1 select left(@nkey,charindex(',',@nkey)-1) set @nkey = stuff(@nkey,1,charindex(',',@nkey),'') end insert into @t1 select @nkey
insert into @t2 select a.ArticleID,b.nkey from article a,@t1 b where a.articleid!=@ArticleID and charindex(','+b.nkey+',',','+a.nkey+',')>0
select @cnt=count(*) from (select ArticleID from @t2 group by ArticleID having count(*)>=@num) t
return @cnt end go select link1=dbo.f_news_linknum(ArticleID,1), link2=dbo.f_news_linknum(ArticleID,2) from article godrop function f_news_linknum drop table article go
我在测试不错,一创建funcation,sql server.就死掉
--生成测试数据 create table article( articleid int,nkey varchar(200), link1 int,link2 int,link3 int,link4 int,link5 int, link6 int,link7 int,link8 int,link9 int,link10 int)insert into article(articleid,nkey) select 1,'北京,上海' insert into article(articleid,nkey) select 2,'山西,上海' insert into article(articleid,nkey) select 3,'北京,陕西' insert into article(articleid,nkey) select 4,'北京,大连' insert into article(articleid,nkey) select 5,'北京,上海' go --创建用户定义函数 create function f_news_linknum(@ArticleID int,@num int) returns int as begin declare @t1 table(nkey varchar(200)) declare @t2 table(ArticleID int,nkey varchar(200)) declare @nkey varchar(200),@cnt int
select @nkey=nkey from article where articleid=@ArticleID
while (charindex(',',@nkey)>0) begin insert into @t1 select left(@nkey,charindex(',',@nkey)-1) set @nkey = stuff(@nkey,1,charindex(',',@nkey),'') end insert into @t1 select @nkey
insert into @t2 select a.ArticleID,b.nkey from article a,@t1 b where a.articleid!=@ArticleID and charindex(','+b.nkey+',',','+a.nkey+',')>0
select @cnt=count(*) from (select ArticleID from @t2 group by ArticleID having count(*)>=@num) t
----------------------------------------------------------------------------------------------------------------
CREATE function f_news_linknum(@ArticleID int)
returns @r table(articleid int,title varchar(1000),classid int,Nkey varchar(200),dateandtime,[匹配度] int)
begin declare @Tags varchar(200);
set @Tags=(select nkey from article where articleid=@ArticleID)
insert into @r
select
top 5 articleid,title,classid,Nkey,dateandtime,count(1)
from
article a,
dbo.GetRecords(@Tags) b
where
charindex(','+b.record+',',','+a.nkey+',')>0
and
ArticleID<>@ArticleID and 匹配度=1
group by
articleid,classid,title,nkey,dateandtime
return @r
end
go
update article set link2=f_news_linknum(articleid,2)
update article set link3=f_news_linknum(articleid,3)
update article set link4=f_news_linknum(articleid,4)
update article set link5=f_news_linknum(articleid,5)
update article set link6=f_news_linknum(articleid,6)
update article set link7=f_news_linknum(articleid,7)
update article set link8=f_news_linknum(articleid,8)
update article set link9=f_news_linknum(articleid,9)
update article set link10=f_news_linknum(articleid,10)
另外不知道我这样做(建立10个字段,又用10个update语句是不是挺笨的)
另外这个函数还不会写呢。
是要这样调用
update article set link1=f_news_linknum(1)
update article set link2=f_news_linknum(2)
update article set link3=f_news_linknum(3)
update article set link4=f_news_linknum(4)
update article set link5=f_news_linknum(5)
update article set link6=f_news_linknum(6)
update article set link7=f_news_linknum(7)
update article set link8=f_news_linknum(8)
update article set link9=f_news_linknum(9)
update article set link10=f_news_linknum(10)
update article set link1=f_news_linknum(articleid,1)
update article set link2=f_news_linknum(articleid,2)
update article set link3=f_news_linknum(articleid,3)
update article set link4=f_news_linknum(articleid,4)
update article set link5=f_news_linknum(articleid,5)
update article set link6=f_news_linknum(articleid,6)
update article set link7=f_news_linknum(articleid,7)
update article set link8=f_news_linknum(articleid,8)
update article set link9=f_news_linknum(articleid,9)
update article set link10=f_news_linknum(articleid,10)
另外不知道我这样做(建立10个字段,又用10个update语句是不是挺笨的)
另外这个函数还不会写呢。
比如有一个相关的就是1
北京,上海
这个记录
按相关数至少是1统计的结果是link1(5,北京有3个,上海有2个,3+2=5)我没数错吧
按相关数至少是2统计的结果是link2(1,北京,上海有有的只有1个相关)我没数错吧
就是说link1是至少有一个相同记录数,link2是至少有一个相同记录数articleid nkey link1 link2 link3 link4 link5 link6 link7 link8 link9 link10
1 北京,上海 3 1 0
2 山西,上海 2 0
3 北京,陕西 3 0
4 北京,大连 3 0
5 北京,上海 3 1
-------------------------------------------------------------------------
create table article(articleid int,nkey varchar(200))
insert into article select 1,'北京,上海'
insert into article select 2,'山西,上海'
insert into article select 3,'北京,陕西'
insert into article select 4,'北京,大连'
insert into article select 5,'北京,上海'
go
create function f_news_linknum(@ArticleID int,@num int)
returns int
as
begin
declare @t1 table(nkey varchar(200))
declare @t2 table(ArticleID int,nkey varchar(200))
declare @nkey varchar(200),@cnt int
select @nkey=nkey from article where articleid=@ArticleID
while (charindex(',',@nkey)>0)
begin
insert into @t1 select left(@nkey,charindex(',',@nkey)-1)
set @nkey = stuff(@nkey,1,charindex(',',@nkey),'')
end
insert into @t1 select @nkey
insert into @t2
select
a.ArticleID,b.nkey
from
article a,@t1 b
where
a.articleid!=@ArticleID
and
charindex(','+b.nkey+',',','+a.nkey+',')>0
select @cnt=count(*)
from (select ArticleID from @t2 group by ArticleID having count(*)>=@num) t
return @cnt
end
go
select
link1=dbo.f_news_linknum(ArticleID,1),
link2=dbo.f_news_linknum(ArticleID,2)
from article
godrop function f_news_linknum
drop table article
go
create table article(
articleid int,nkey varchar(200),
link1 int,link2 int,link3 int,link4 int,link5 int,
link6 int,link7 int,link8 int,link9 int,link10 int)insert into article(articleid,nkey) select 1,'北京,上海'
insert into article(articleid,nkey) select 2,'山西,上海'
insert into article(articleid,nkey) select 3,'北京,陕西'
insert into article(articleid,nkey) select 4,'北京,大连'
insert into article(articleid,nkey) select 5,'北京,上海'
go
--创建用户定义函数
create function f_news_linknum(@ArticleID int,@num int)
returns int
as
begin
declare @t1 table(nkey varchar(200))
declare @t2 table(ArticleID int,nkey varchar(200))
declare @nkey varchar(200),@cnt int
select @nkey=nkey from article where articleid=@ArticleID
while (charindex(',',@nkey)>0)
begin
insert into @t1 select left(@nkey,charindex(',',@nkey)-1)
set @nkey = stuff(@nkey,1,charindex(',',@nkey),'')
end
insert into @t1 select @nkey
insert into @t2
select
a.ArticleID,b.nkey
from
article a,@t1 b
where
a.articleid!=@ArticleID
and
charindex(','+b.nkey+',',','+a.nkey+',')>0
select @cnt=count(*)
from (select ArticleID from @t2 group by ArticleID having count(*)>=@num) t
return @cnt
end
go
--调用函数实现更新操作
update article
set
link1 =dbo.f_news_linknum(ArticleID, 1),
link2 =dbo.f_news_linknum(ArticleID, 2),
link3 =dbo.f_news_linknum(ArticleID, 3),
link4 =dbo.f_news_linknum(ArticleID, 4),
link5 =dbo.f_news_linknum(ArticleID, 5),
link6 =dbo.f_news_linknum(ArticleID, 6),
link7 =dbo.f_news_linknum(ArticleID, 7),
link8 =dbo.f_news_linknum(ArticleID, 8),
link9 =dbo.f_news_linknum(ArticleID, 9),
link10=dbo.f_news_linknum(ArticleID,10)
go--查看更新操作结果
select * from article
go/*
articleid nkey link1 link2 link3 link4 link5 link6 link7 link8 link9 link10
1 北京,上海 4 1 0 0 0 0 0 0 0 0
2 山西,上海 2 0 0 0 0 0 0 0 0 0
3 北京,陕西 3 0 0 0 0 0 0 0 0 0
4 北京,大连 3 0 0 0 0 0 0 0 0 0
5 北京,上海 4 1 0 0 0 0 0 0 0 0
*/--删除测试数据
drop function f_news_linknum
drop table article
go
http://community.csdn.net/Expert/topic/4460/4460680.xml?temp=.46259711万条数据,光执行一个
update article set link1=dbo.f_news_linknum(ArticleID,1)
用了12分19秒。能再快点吗,比如10个都执行了能不能优化到10分钟以内。还有就是如果nkey这个字段是空的,则link0.....link10都记成0我整个的这个统计过程是不是有什么地方还可以改进一下呢。