try:
-----------------------------------------------------------------------------------------------------------------------------------
CREATE function f_news_otherArticle(@ArticleID int,@userid int,@num int)
returns varchar(500)
as
begin
declare @otherArticle varchar(500)
set @otherArticle = ''
select
@otherArticle=@@otherArticle+','+a.ArticleID
from
T_news_ArticleReadLog a
where
a.userID=@userid and a.ArticleID<>@ArticleID
order by
(select count(*) from T_news_ArticleReadLog where ArticleID=a.ArticleID group by ArticleID) desc
set @otherArticle=stuff(@otherArticle,1,1,'')
return @otherArticle
end
go
-----------------------------------------------------------------------------------------------------------------------------------
CREATE function f_news_otherArticle(@ArticleID int,@userid int,@num int)
returns varchar(500)
as
begin
declare @otherArticle varchar(500)
set @otherArticle = ''
select
@otherArticle=@@otherArticle+','+a.ArticleID
from
T_news_ArticleReadLog a
where
a.userID=@userid and a.ArticleID<>@ArticleID
order by
(select count(*) from T_news_ArticleReadLog where ArticleID=a.ArticleID group by ArticleID) desc
set @otherArticle=stuff(@otherArticle,1,1,'')
return @otherArticle
end
go
必须声明变量 '@@otherArticle'。
服务器: 消息 156,级别 15,状态 1,过程 f_news_otherArticle,行 15
在关键字 'desc' 附近有语法错误。
create table Test_article(ArticleID int,otherArticle varchar(30))
insert into Test_article
select 1,''union all
select 2,''union all
select 3,''union all
select 4,''
gocreate table Test_T_news_ArticleReadLog(articleID int,userid int)
insert into Test_T_news_ArticleReadLog
select 1,101 union all
select 1,102 union all
select 1,103 union all
select 2,104 union all
select 2,105 union all
select 2,106 union all
select 3,107 union all
select 3,108 union all
select 3,109 union all
select 4,110 union all
select 4,111 union all
select 4,112
go
--创建测试存储过程
create procedure sp_news_otherArticle @ArticleID int
as
declare @_otherArticle varchar(30)
set @_otherArticle=''
select @_otherArticle=@_otherArticle+cast(userid as varchar)+',' from Test_T_news_ArticleReadLog where articleID=@ArticleID
set @_otherArticle=left(@_otherArticle,len(@_otherArticle)-1)
update Test_article set otherArticle=@_otherArticle where articleID=@ArticleID
go
--测试语句
exec sp_news_otherArticle 1
exec sp_news_otherArticle 2
exec sp_news_otherArticle 3
exec sp_news_otherArticle 4
select * from Test_article
--删除测试表和测试存储过程
drop table Test_article,Test_T_news_ArticleReadLog
drop procedure sp_news_otherArticle
/*
ArticleID otherArticle
----------- ------------------------------
1 101,102,103
2 104,105,106
3 107,108,109
4 110,111,112(所影响的行数为 4 行)
*/
另外子陌老大没明白我的意思比如对于一个ArticleID=199的文章我首先到T_news_ArticleReadLog表中找出有多少个userid曾经阅读过这篇文章,当然userid可能重复。(所以我想可能要做一些处理)
然后我在继续在T_news_ArticleReadLog表中找出这些人都阅读过哪些文章,并按ArticleID分组看看这些人感兴趣的文章里面哪些文章阅读的次数最高,然后把排名前num的排列成
13,2234,23,54,65这种形式,写道Article表中。这样以后我在网页上显示这篇文章的时候就很容易取出“阅读了某篇文章的用户还阅读了哪些文章”不知道我说明白了没有,另外有人觉得我的这种方式来做“统计阅读了某篇文章的用户还阅读了哪些文章”是不是正确合理的方式。当然我还有替他的方式来做相关性阅读,这个帖子只讨论“阅读了某篇文章的用户还阅读了哪些文章”这个问题
--要达到这个目的这样不就可以了:--创建1个测试表
create table Test_T_news_ArticleReadLog(articleID int,userid int)
insert into Test_T_news_ArticleReadLog
select 1,101 union all
select 1,102 union all
select 1,103 union all
select 2,101 union all
select 2,105 union all
select 2,106 union all
select 3,101 union all
select 3,108 union all
select 3,109 union all
select 4,103 union all
select 4,111 union all
select 4,112
go
--创建测试函数
create function f_news_otherArticle(@ArticleID int,@userid int)
returns varchar(30)
as
begin
declare @re varchar(30)
set @re=''
select @re=@re+cast(articleID as varchar)+',' from Test_T_news_ArticleReadLog where userid=@userid
and articleID<>@ArticleID
set @re=left(@re,len(@re)-1)
return @re
end
go
--测试语句
select dbo.f_news_otherArticle(1,101)
--删除测试表和测试函数
drop table Test_T_news_ArticleReadLog
drop function f_news_otherArticle/*
------------------------------
2,3(所影响的行数为 1 行)
*/
insert into T_news_ArticleReadLog select 1,1
insert into T_news_ArticleReadLog select 1,2
insert into T_news_ArticleReadLog select 1,3
insert into T_news_ArticleReadLog select 2,4
insert into T_news_ArticleReadLog select 2,5
insert into T_news_ArticleReadLog select 2,6
insert into T_news_ArticleReadLog select 2,7
insert into T_news_ArticleReadLog select 3,1
insert into T_news_ArticleReadLog select 3,2
insert into T_news_ArticleReadLog select 3,3
insert into T_news_ArticleReadLog select 4,4
insert into T_news_ArticleReadLog select 4,5
insert into T_news_ArticleReadLog select 4,6
insert into T_news_ArticleReadLog select 4,7
insert into T_news_ArticleReadLog select 5,1
insert into T_news_ArticleReadLog select 5,2
insert into T_news_ArticleReadLog select 5,3
insert into T_news_ArticleReadLog select 6,4
insert into T_news_ArticleReadLog select 6,5
insert into T_news_ArticleReadLog select 7,6
insert into T_news_ArticleReadLog select 7,7
goCREATE function f_news_otherArticle(@ArticleID int,@userid int)
returns varchar(500)
as
begin
declare @otherArticle varchar(500)
set @otherArticle = ''
select
@otherArticle=@otherArticle+','+rtrim(a.ArticleID)
from
T_news_ArticleReadLog a
where
a.userID=@userid and a.ArticleID<>@ArticleID
set @otherArticle=stuff(@otherArticle,1,1,'')
return @otherArticle
end
goselect *,dbo.f_news_otherArticle(ArticleID,userid) from T_news_ArticleReadLog
go
/*
1 1 3,5
1 2 3,5
1 3 3,5
2 4 4,6
2 5 4,6
2 6 4,7
2 7 4,7
3 1 1,5
3 2 1,5
3 3 1,5
4 4 2,6
4 5 2,6
4 6 2,7
4 7 2,7
5 1 1,3
5 2 1,3
5 3 1,3
6 4 2,4
6 5 2,4
7 6 2,4
7 7 2,4
*/
drop function f_news_otherArticle
drop table T_news_ArticleReadLog
go
122,1
122,1
这种情况,一篇文章一个用户看了好几次。
另外我要update article set otherArticle=f_news_otherArticle(ArticleID)这样用,根本没有什么userid作为参数。
所以根本就没有userid这个参数
------------------------------------------------------------------------------------------------------------------------------
--生成测试数据
create table T_news_ArticleReadLog(ArticleID int,userid int)
insert into T_news_ArticleReadLog select 1,1
insert into T_news_ArticleReadLog select 1,2
insert into T_news_ArticleReadLog select 1,3
insert into T_news_ArticleReadLog select 2,4
insert into T_news_ArticleReadLog select 2,5
insert into T_news_ArticleReadLog select 2,6
insert into T_news_ArticleReadLog select 2,7
insert into T_news_ArticleReadLog select 3,1
insert into T_news_ArticleReadLog select 3,2
insert into T_news_ArticleReadLog select 3,3
insert into T_news_ArticleReadLog select 4,4
insert into T_news_ArticleReadLog select 4,5
insert into T_news_ArticleReadLog select 4,6
insert into T_news_ArticleReadLog select 4,7
insert into T_news_ArticleReadLog select 5,1
insert into T_news_ArticleReadLog select 5,2
insert into T_news_ArticleReadLog select 5,3
insert into T_news_ArticleReadLog select 6,4
insert into T_news_ArticleReadLog select 6,5
insert into T_news_ArticleReadLog select 7,6
insert into T_news_ArticleReadLog select 7,7
go--创建用户定义函数
CREATE function f_news_otherArticle(@ArticleID int,@userid int,@num int)
returns varchar(500)
as
begin
declare @otherArticle varchar(500)
set @otherArticle = ''
declare @t table(id int identity(1,1),ArticleID int,cnt int)
insert into @t
select
a.ArticleID,cnt=count(*)
from
T_news_ArticleReadLog a
where
a.userID in(select userID from T_news_ArticleReadLog where ArticleID=@ArticleID)
and
a.ArticleID<>@ArticleID
group by
a.ArticleID
order by
cnt desc
select @otherArticle=@otherArticle+','+rtrim(ArticleID) from @t where id<=@num
set @otherArticle=stuff(@otherArticle,1,1,'')
return @otherArticle
end
go
--执行查询并输出结果
select *,dbo.f_news_otherArticle(ArticleID,userid,2) from T_news_ArticleReadLog
go/*
1 1 3,5
1 2 3,5
1 3 3,5
2 4 4,6
2 5 4,6
2 6 4,6
2 7 4,6
3 1 1,5
3 2 1,5
3 3 1,5
4 4 2,6
4 5 2,6
4 6 2,6
4 7 2,6
5 1 1,3
5 2 1,3
5 3 1,3
6 4 2,4
6 5 2,4
7 6 2,4
7 7 2,4
*/
--删除测试数据
drop function f_news_otherArticle
drop table T_news_ArticleReadLog
go
再仔细看一遍。
把你的笔误userid参数去掉之后,CREATE function f_news_otherArticle(@ArticleID int,@num int)
我是不是可以用
update article set otherArticle=f_news_otherArticle(ArticleID)了
------------------------------------------------------------------------------------------------------------------------------
--生成测试数据
create table T_news_ArticleReadLog(ArticleID int,userid int)
insert into T_news_ArticleReadLog select 1,1
insert into T_news_ArticleReadLog select 1,2
insert into T_news_ArticleReadLog select 1,3
insert into T_news_ArticleReadLog select 2,4
insert into T_news_ArticleReadLog select 2,5
insert into T_news_ArticleReadLog select 2,6
insert into T_news_ArticleReadLog select 2,7
insert into T_news_ArticleReadLog select 3,1
insert into T_news_ArticleReadLog select 3,2
insert into T_news_ArticleReadLog select 3,3
insert into T_news_ArticleReadLog select 4,4
insert into T_news_ArticleReadLog select 4,5
insert into T_news_ArticleReadLog select 4,6
insert into T_news_ArticleReadLog select 4,7
insert into T_news_ArticleReadLog select 5,1
insert into T_news_ArticleReadLog select 5,2
insert into T_news_ArticleReadLog select 5,3
insert into T_news_ArticleReadLog select 6,4
insert into T_news_ArticleReadLog select 6,5
insert into T_news_ArticleReadLog select 7,6
insert into T_news_ArticleReadLog select 7,7
go--创建用户定义函数
CREATE function f_news_otherArticle(@ArticleID int,@num int)
returns varchar(500)
as
begin
declare @otherArticle varchar(500)
set @otherArticle = ''
declare @t table(id int identity(1,1),ArticleID int,cnt int)
insert into @t
select
a.ArticleID,cnt=count(*)
from
T_news_ArticleReadLog a
where
a.userID in(select userID from T_news_ArticleReadLog where ArticleID=@ArticleID)
and
a.ArticleID<>@ArticleID
group by
a.ArticleID
order by
cnt desc
select @otherArticle=@otherArticle+','+rtrim(ArticleID) from @t where id<=@num
set @otherArticle=stuff(@otherArticle,1,1,'')
return @otherArticle
end
go
--执行查询并输出结果
select *,dbo.f_news_otherArticle(ArticleID,2) from T_news_ArticleReadLog
go/*
1 1 3,5
1 2 3,5
1 3 3,5
2 4 4,6
2 5 4,6
2 6 4,6
2 7 4,6
3 1 1,5
3 2 1,5
3 3 1,5
4 4 2,6
4 5 2,6
4 6 2,6
4 7 2,6
5 1 1,3
5 2 1,3
5 3 1,3
6 4 2,4
6 5 2,4
7 6 2,4
7 7 2,4
*/
--删除测试数据
drop function f_news_otherArticle
drop table T_news_ArticleReadLog
go
set @num = xx
update article set otherArticle=f_news_otherArticle(ArticleID,@num)