--关键字完全匹配查找比较好弄 --否则需要涉及到中文分词 create table AC_Article ( ArticleId int, IsEnable bit, IsPublish bit, publishTime datetime, KeyWords nvarchar(100) )insert into AC_Article select 1,'true','true','2010-1-1 10:59:00',N'联合卡车,卡车' union all select 2,'true','true','2010-1-2 10:59:00',N'联合卡车,货车,卡车' union all select 3,'true','true','2010-1-3 10:59:00',N'东风,卡车,油车' union all select 4,'true','true','2010-1-4 10:59:00',N'联合卡车,搅拌车,自卸,轻卡' union all select 5,'false','false','2011-1-5 11:11:11',N'联合卡车,卡车'
GOdeclare @id int, @sql nvarchar(100) declare @t table (keywords nvarchar(100)) set @id=1 --正在查看的文章id select @sql= 'select N'''+replace(keywords,',',''' union all select N''')+'''' from AC_Article where ArticleId=@idinsert into @t exec (@sql)select top 2 ArticleId ,IsEnable ,IsPublish ,publishTime ,KeyWords from ( select A.keywords as keywo ,B.*, sortid=row_number() over (partition by B.ArticleId order by publishTime) from @t as A left join AC_Article as B on charindex(','+A.keywords+',',','+B.keywords+',')>0 and B.IsEnable='true' and B.IsPublish='true' where B.ArticleId<>@id and B.ArticleId is not null ) as X order by sortid desc, publishTime descGO drop table AC_Article
--关键词--文章关系表 insert [AC_Article_KeyWords_Relation] select b.KeyWords,a.ArticleId from (select ArticleId,KeyWords=case when isnull(KeyWords,'') = '' then '卡车' else KeyWords end from [AC_Article] where IsPublish='true' and IsEnable='true') a cross apply ( select KeyWords=SUBSTRING(a.KeyWords,number,CHARINDEX(',',a.KeyWords+',',number+1)-number) from master..spt_values where CHARINDEX(',',','+a.KeyWords,number) = number AND type = 'P' ) b order by b.KeyWords我现在可以查询到这种情况了select articleId,count(*) as keycount from ac_article_keywords_relation where keywords in(select keywords from [ac_article_keywords_relation] where articleId=12594) group by articleId order by keycount desc,articleId desc谁能帮我把查出来的结果如果keycount的值相同那么按照AC_article表中的publishTime 倒序排啊,急用啊,先谢谢了
create table AC_Article ( ArticleId int, IsEnable bit, IsPublish bit, publishTime datetime, KeyWords varchar(100) )insert into AC_Article select 1,'true','true','2010-1-1 10:59:00','联合卡车,卡车' union all select 2,'true','true','2010-1-2 10:59:00','联合卡车,货车,卡车' union all select 3,'true','true','2010-1-3 10:59:00','东风,卡车,油车' union all select 4,'true','true','2010-1-4 10:59:00','联合卡车,搅拌车,自卸,轻卡 ' union all select 5,'false','false','2011-1-5 11:11:11','联合卡车,卡车' go select top 2 ArticleId from( select ArticleId from AC_Article where ','+KeyWords like '%,卡车' union all select ArticleId from AC_Article where ','+KeyWords like '%,联合卡车' union all select ArticleId from AC_Article where ','+KeyWords like '%,轻卡' union all select ArticleId from AC_Article where ','+KeyWords like '%,自卸' )t group by ArticleId order by count(*) desc go drop table AC_Article /* ArticleId ----------- 4 2(2 行受影响)*/
--关键字完全匹配查找比较好弄
--否则需要涉及到中文分词
create table AC_Article
(
ArticleId int,
IsEnable bit,
IsPublish bit,
publishTime datetime,
KeyWords nvarchar(100)
)insert into AC_Article
select 1,'true','true','2010-1-1 10:59:00',N'联合卡车,卡车' union all
select 2,'true','true','2010-1-2 10:59:00',N'联合卡车,货车,卡车' union all
select 3,'true','true','2010-1-3 10:59:00',N'东风,卡车,油车' union all
select 4,'true','true','2010-1-4 10:59:00',N'联合卡车,搅拌车,自卸,轻卡' union all
select 5,'false','false','2011-1-5 11:11:11',N'联合卡车,卡车'
GOdeclare @id int, @sql nvarchar(100)
declare @t table (keywords nvarchar(100))
set @id=1 --正在查看的文章id
select @sql= 'select N'''+replace(keywords,',',''' union all select N''')+'''' from AC_Article where ArticleId=@idinsert into @t
exec (@sql)select top 2 ArticleId ,IsEnable ,IsPublish ,publishTime ,KeyWords
from
(
select A.keywords as keywo ,B.*, sortid=row_number() over (partition by B.ArticleId order by publishTime) from @t as A
left join AC_Article as B
on charindex(','+A.keywords+',',','+B.keywords+',')>0
and B.IsEnable='true' and B.IsPublish='true'
where B.ArticleId<>@id
and B.ArticleId is not null
) as X
order by sortid desc, publishTime descGO
drop table AC_Article
--关键词--文章关系表
insert [AC_Article_KeyWords_Relation]
select b.KeyWords,a.ArticleId
from (select ArticleId,KeyWords=case when isnull(KeyWords,'') = '' then '卡车' else KeyWords end from [AC_Article] where IsPublish='true' and IsEnable='true') a
cross apply (
select KeyWords=SUBSTRING(a.KeyWords,number,CHARINDEX(',',a.KeyWords+',',number+1)-number)
from master..spt_values
where CHARINDEX(',',','+a.KeyWords,number) = number AND type = 'P'
) b order by b.KeyWords我现在可以查询到这种情况了select articleId,count(*) as keycount from ac_article_keywords_relation where keywords in(select keywords from [ac_article_keywords_relation] where articleId=12594) group by articleId order by keycount desc,articleId desc谁能帮我把查出来的结果如果keycount的值相同那么按照AC_article表中的publishTime 倒序排啊,急用啊,先谢谢了
(
ArticleId int,
IsEnable bit,
IsPublish bit,
publishTime datetime,
KeyWords varchar(100)
)insert into AC_Article
select 1,'true','true','2010-1-1 10:59:00','联合卡车,卡车' union all
select 2,'true','true','2010-1-2 10:59:00','联合卡车,货车,卡车' union all
select 3,'true','true','2010-1-3 10:59:00','东风,卡车,油车' union all
select 4,'true','true','2010-1-4 10:59:00','联合卡车,搅拌车,自卸,轻卡 ' union all
select 5,'false','false','2011-1-5 11:11:11','联合卡车,卡车'
go
select top 2 ArticleId from(
select ArticleId from AC_Article where ','+KeyWords like '%,卡车'
union all
select ArticleId from AC_Article where ','+KeyWords like '%,联合卡车'
union all
select ArticleId from AC_Article where ','+KeyWords like '%,轻卡'
union all
select ArticleId from AC_Article where ','+KeyWords like '%,自卸'
)t
group by ArticleId order by count(*) desc
go
drop table AC_Article
/*
ArticleId
-----------
4
2(2 行受影响)*/
----------- ----------------------- -----------
4 2010-01-04 10:59:00.000 3
3 2010-01-03 10:59:00.000 2(2 行受影响)