create table news(keys nvarchar(200))
insert into news select '房地产+城建+监理'
insert into news select '城建+监理'
insert into news select '城建'
insert into news select '新华社'declare @keys nvarchar(100)
set @keys='房地产+城建+监理'declare @sql nvarchar(4000)
set @sql='select * from news where keys like ''%'+replace(@keys,'+','%'' or keys like ''%')+'%'''
exec(@sql)/*
keys
--------------------------
房地产+城建+监理
城建+监理
城建
*/drop table news
insert into news select '房地产+城建+监理'
insert into news select '城建+监理'
insert into news select '城建'
insert into news select '新华社'declare @keys nvarchar(100)
set @keys='房地产+城建+监理'declare @sql nvarchar(4000)
set @sql='select * from news where keys like ''%'+replace(@keys,'+','%'' or keys like ''%')+'%'''
exec(@sql)/*
keys
--------------------------
房地产+城建+监理
城建+监理
城建
*/drop table news
解决方案 »
- 【求助】如何判断一个表在C#中是否成功创建
- 大家有什么好的存储过程优化方法吗?
- sql2000需要重新启动服务才能连接
- 在SQL SERVER6.5中备份的数据能不能在SQL SERVER7.0中还原?在SQL SERVER7.0中备份的数据能不能在SQL SERVER2000中还原?
- 如何搜索一个表中有为空记录的信息
- msSQL更新数据问题(非简单更新)
- 请高手指点迷津
- 类似行列转换问题
- 装sqlserver2000时提示挂起,重起后还是不行
- 在LISTBOX的MOUSEMOVE事件中怎样写鼠标移到哪条item,即选取该item
- 烦死了.SQL server 2000可不可以升级啊.我的版本是8.00.194;我想升级大sp3,下了个补丁.装了.版本还是一样!
- 求助! 如何查询出有相同字段的数据行?谢了
楼主的意思可能不是这样的,他输入时并没有中间+号,就像搜索引擎一样,输入中文,
而要求在程序中实现自动断词功能,比如客户输入:房地产城建监理,而程序自动把它分成三部分.
房地产\城建\监理
如现在有通过后台添加了一条新闻A,他的关键字有三个,房地产,城建
监理;则在数据库里A的keys为"房地产+城建+监理"。现在想查询和新闻A关键字类似的相关新闻如 记录1 keys为 "房地产+城建+监理"
记录2 keys为 "城建+监理"
记录3 keys为 "城建"
记录4 keys为 "新华社"希望在检索的时候记录1,记录2,记录3 都应该能检索到,因为这三条记录包含了全部或者部分关键字,这是最低要求。最好还能实现记录1的优先级大于其它记录,因为它匹配的关键字多。要求就是这样的。
--功能:分割字符串,返回字符串记录集
--@Str:要分割的字符串
--@Split:分割字符
--@Dist:是否去除重复项
*****************************************/
if not object_id('GetRecords') is null
drop function GetRecords
gocreate function GetRecords(
@str varchar(2000),
@split varchar(20),
@Dist bit)
returns @Rec table (Record varchar(40))
as
begin
declare @s varchar(2000)
declare @r varchar(2000)
declare @i varchar(2000)
Declare @Len smallintselect @Len=len(@Split)
set @s=@str
set @i=CHARINDEX(@split,@s)
while @i>0
begin
set @r=left(@s,@i-1)
if @Dist=1
begin
if not exists(Select 1 from @rec where Record=@r)
insert @Rec values (@R)
end
else
begin
insert @Rec values (@R)
end
set @s=right(@s,len(@s)-@i-(@len-1))
set @i=CHARINDEX(@Split,@s)
end
if len(@s)>0
if @Dist=1
begin
if not exists(Select 1 from @rec where Record=@s)
insert @Rec values (@s)
end
else
begin
insert @Rec values (@s)
end
return
end
go
create table news(keys nvarchar(200))
insert into news select '房地产+城建+监理'
insert into news select '城建+监理'
insert into news select '城建'
insert into news select '新华社'select * from news
--定义四个关键字列(如果能临时放一个表中处理将更加简单)Declare @Keys1 varchar(100)
Declare @Keys2 varchar(100)
Declare @Keys3 varchar(100)
Declare @Keys4 varchar(100)set @keys1='房地产+城建+监理'
set @keys2='城建+监理'
set @keys3='城建'
set @keys4='新华社'select keys from
(select keys,c=count(*) from
(select * from news a
inner join dbo.GetRecords(@keys1,'+',0) b
on a.keys like '%'+b.record+'%'
union
select * from news a
inner join dbo.GetRecords(@keys2,'+',0) b
on a.keys like '%'+b.record+'%'
union
select * from news a
inner join dbo.GetRecords(@keys3,'+',0) b
on a.keys like '%'+b.record+'%'
union
select * from news a
inner join dbo.GetRecords(@keys4,'+',0) b
on a.keys like '%'+b.record+'%')a
group by keys)a
order by c desc
Declare @Keys2 varchar(100)
Declare @Keys3 varchar(100)
Declare @Keys4 varchar(100)
Declare @Keys varchar(400)set @keys1='房地产+城建+监理'
set @keys2='城建+监理'
set @keys3='城建'
set @keys4='新华社'select @keys=@Keys1+'+'+@keys2+'+'+@keys3+'+'+@keys4select keys from
(select keys,c=count(*) from
(select * from news a
inner join dbo.GetRecords(@keys,'+',1) b
on a.keys like '%'+b.record+'%'
)a
group by keys)a
order by c desc
十分感谢,小弟笨拙,有些地方没有看懂每条新闻的关键字不同,针对某条新闻A,我需要能从A中提取出其关键字序列B,
然后再查询得到news表内具有关键字序列B所包含的关键字的所有的记录。
请问你说的方法能实现吗
@str varchar(2000), -------被判断串
@KeyStr varchar(256), -------关键字串
@split varchar(2))
returns @P int
AS
BEGIN
DECLARE @i int,@idx int,@tmpStr varchar(2000),@Keyw varchar(256)
SELECT @i = 0, @idx = CHARINDEX(@split, @KeyStr),@tmpStr = @KeyStr
WHILE @idx>0
@Keyw = SUBSTRING(@tmpStr,1,@idx - 1)
@tmpStr = SUBSTRING(@tmpStr,@idx + 1, LENGTH(@tmpStr) - @idx)
IF CHARINDEX(@KeyW,@str)>0
SELECT @i=@i+1
@idx = CHARINDEX(@split, @tmpStr)
END
RETURN
END
GOcreate table #news(keys nvarchar(200),newss varchar(2000))
go
insert into #news select '房地产+城建+监理','fdafdafdsafa'
insert into #news select '城建+监理','fdasfdsafdsafd'
insert into #news select '城建','fdsafdsafdsaf'
insert into #news select '新华社','fdsafdasfdasfdasfdsaf'
godeclare @keywww varchar(200)
select @key = keys from #news where keys='房地产+城建+监理'
select *, Powerc=PowerOfCp(keys,@key,'+') into #result from #news
select * from #result where powerc>0 order by powerc desc
CREATE FUNCTION dbo.usf_Keys(@keyWords NVARCHAR(4000),@keyStatement NVARCHAR(4000))
returns INT
AS
BEGIN
DECLARE @intIndex AS INT
DECLARE @strKey AS NVARCHAR(4000)
DECLARE @intResult AS INT
SET @intResult=0
SET @intIndex=1
IF RIGHT(@keyWords,1)<>'+'
BEGIN
SET @keyWords=@keyWords+'+'
END
IF LEFT(@keyWords,1)<>'+'
BEGIN
SET @keyWords='+'+@keyWords
END
WHILE @intIndex>0
BEGIN
IF CHARINDEX('+',@keyWords,@intIndex+1)>0
BEGIN
SET @strKey=SUBSTRING(@keyWords,@intIndex,CHARINDEX('+',@keyWords,@intIndex+1)-@intIndex+1)
IF CHARINDEX(@strKey,'+'+@keyStatement+'+')>0
BEGIN
SET @intResult=@intResult+1
END
END
SET @intIndex=CHARINDEX('+',@keyWords,@intIndex+1)
END
RETURN @intResult
END--测试数据
DECLARE @t TABLE (keyx NVARCHAR(2000))
INSERT INTO @t VALUES('城建')
INSERT INTO @t VALUES('新华社')
INSERT INTO @t VALUES('房地产+城建+监理')
INSERT INTO @t VALUES('城建+监理')--普通查询
SELECT keyx,dbo.usf_Keys('房地产+城建+监理',keyx)
FROM @t城建 1
新华社 0
房地产+城建+监理 3
城建+监理 2--只取包含关键字的,而且按匹配程度倒排序
SELECT keyx,dbo.usf_Keys('房地产+城建+监理',keyx)
FROM @t
WHERE dbo.usf_Keys('房地产+城建+监理',keyx)>0
ORDER BY dbo.usf_Keys('房地产+城建+监理',keyx) DESC房地产+城建+监理 3
城建+监理 2
城建 1
然后再查询得到news表内具有关键字序列B所包含的关键字的所有的记录。
请问你说的方法能实现吗
--------------------------------------------------------------------
已经实现了呀,只是生成关键字序列的方法不同而已,换成你自己的方法就可以啦--比如你得到了关键字序列@keys
--只要代入就可以啦
select keys from
(select keys,c=count(*) from
(select * from news a
inner join dbo.GetRecords(@keys,'+',1) b
on a.keys like '%'+b.record+'%'
)a
group by keys)a
order by c desc
insert into news select '房地产+城建+监理'
insert into news select '城建+监理'
insert into news select '城建'
insert into news select '新华社'select * from news where PATINDEX('%'+keys+'%', '房地产+城建+监理')>0
drop table news/*
房地产+城建+监理
城建+监理
城建
*/
INSERT [Table1] ( [ID] , [Name] , [PostDate] ) VALUES ( 2 , 'abc' , '2002-02-01 00:00:00.000' )
INSERT [Table1] ( [ID] , [Name] , [PostDate] ) VALUES ( 3 , 'efg' , '2002-05-01 00:00:00.000' )
INSERT [Table1] ( [ID] , [Name] , [PostDate] ) VALUES ( 4 , 'abc' , '2002-01-20 00:00:00.000' )
INSERT [Table1] ( [ID] , [Name] , [PostDate] ) VALUES ( 5 , 'efg' , '2002-06-12 00:00:00.000' )Select A.*,B.ID
From (select Distinct Name,Max(postdate) As PostDate From Table1 Group by Name) A,Table1 B
where A.Name = B.Name and A.postdate = B.PostDate Order by B.ID