解决方案 »
- SQL里如何截取中文字符串 100分
- 怎么用sql了执行一台服务器上的数据库的某表记录添加到另外一台服务器上的数据库的某表,表的字段是一样的?
- 请问如何在一个特定的时间让数据库自动执行一些命令
- 删除事务日志文件的问题,重新建事务日志文件,请高手们帮忙?
- 如何做此表?
- 求助:表A表B结构一致,如果A的数据同样存在B 中,删除A 中的数据行,请问怎么实现最简单?
- 高分求 关于SQL SERVER JOB的问题
- sql专家来看
- 数据库置疑,数据恢复失败,求高手解决,价格面议
- 大侠们,怎么将sql server库放到租赁的虚拟主机空间上,该空间是支持ms sql。
- 表结构一样,所有者不一样,如何把数据完整的转移过去
- 关于这段游标写法,为什么报只读?The cursor is READ ONLY.
INNER JOIN @Products b
ON CHARINDEX(b.keyWord,a.Title)>0
WHERE CONTAINS(Title,@contains)
GROUP BY a.ID,Title,AddDate
ORDER BY Sum(keyNum) DESC,COUNT(DISTINCT keyword),AddDate desc
INNER JOIN @Products b
ON CHARINDEX(b.keyWord,a.Title)>0
WHERE CONTAINS(Title,@contains)
GROUP BY a.ID,Title,AddDate
ORDER BY Sum(keyNum) DESC,COUNT(DISTINCT keyword),AddDate desc
消息 1087,级别 15,状态 2,第 2 行
必须声明表变量 "@Products"。
set @sql=N'SELECT a.ID,Title,AddDate from ' +@tablename+ ' a
INNER JOIN '+@Products+' b
ON CHARINDEX(b.keyWord,a.Title)>0
WHERE CONTAINS(Title,'+@contains+')
GROUP BY a.ID,Title,AddDate
ORDER BY Sum(keyNum) DESC,COUNT(DISTINCT keyword),AddDate desc'
如果写成这样的话
编译通不过:必须声明标量变量 @Products
@Products是一个根据关键词顺序生成的权重表,是一个临时表
alter PROCEDURE [dbo].[NewsSearch]
(@splitstr varchar(1000)=null) as --定义一个临时表,存储拆分后的关键词
DECLARE @Products TABLE(id int identity ,keyWord nvarchar(255),keyNum int)
declare @sum varchar(20);--根据拆分符拆分后的关键词
declare @i int;--循环次数 set @sum='';
set @i=1;
declare @NewStr varchar(2000);
declare @KeyNum int;--关键词数量
set @NewStr=replace(@splitstr,'$','$$');--累加拆分符
set @KeyNum=len(@NewStr)-len(@splitstr);--获取关键词数量
declare @contains varchar(2000);
set @contains='ISABOUT ('; while @i<=len(@splitstr)
begin
if substring(@splitstr,@i,1)='$'
begin
set @i=@i+1
insert @Products select @sum,power(@KeyNum,2)--将关键词与权值存储到临时表中
set @contains=@contains+@sum+' weight (0.'+ CONVERT(varchar(6),power(@KeyNum,2))+'),'
set @KeyNum=@KeyNum-1;--倒排值递减
set @sum=''
end
else
begin
set @sum=@sum+substring(@splitstr,@i,1)
set @i=@i+1
end
end
set @contains=substring(@contains,1,len(@contains)-1)+')'declare @tablename varchar(30)
set @tablename='FS_News'
declare @sql nvarchar(2000)
set @sql=N'SELECT a.ID,Title,AddDate from ' +@tablename+ ' a
INNER JOIN '+@Products+' b
ON CHARINDEX(b.keyWord,a.Title)>0
WHERE CONTAINS(Title,'+@contains+')
GROUP BY a.ID,Title,AddDate
ORDER BY Sum(keyNum) DESC,COUNT(DISTINCT keyword),AddDate desc'print @sqlexec sp_executesql @sql
可以了 ,谢谢!
alter PROCEDURE [dbo].[NewsSearch]
(@splitstr varchar(1000)=null) as
drop table ##Products
create table ##Products(id int identity ,keyWord nvarchar(255),keyNum int) --定义一个临时表,存储拆分后的关键词
--DECLARE ##Products TABLE(id int identity ,keyWord nvarchar(255),keyNum int)
declare @sum varchar(20);--根据拆分符拆分后的关键词
declare @i int;--循环次数 set @sum='';
set @i=1;
declare @NewStr varchar(2000);
declare @KeyNum int;--关键词数量
set @NewStr=replace(@splitstr,'$','$$');--累加拆分符
set @KeyNum=len(@NewStr)-len(@splitstr);--获取关键词数量
declare @contains varchar(2000);
set @contains=''''; while @i<=len(@splitstr)
begin
if substring(@splitstr,@i,1)='$'
begin
set @i=@i+1
insert ##Products select @sum,power(@KeyNum,2)--将关键词与权值存储到临时表中
set @contains=@contains+'"'+@sum+'" or '
set @KeyNum=@KeyNum-1;--倒排值递减
set @sum=''
end
else
begin
set @sum=@sum+substring(@splitstr,@i,1)
set @i=@i+1
end
end
set @contains=substring(@contains,1,len(@contains)-3)+''''declare @tablename varchar(30)
set @tablename='FS_News'
declare @sql nvarchar(2000)
set @sql=N'SELECT a.ID,Title,AddDate from ' +@tablename+ ' a
INNER JOIN ##Products b
ON CHARINDEX(b.keyWord,a.Title)>0
WHERE CONTAINS(Title,'+@contains+')
GROUP BY a.ID,Title,AddDate
ORDER BY Sum(keyNum) DESC,COUNT(DISTINCT keyword),AddDate desc'print @contains
print @sqlexec sp_executesql @sql