if not exists(select * from sysobjects where name='AC_Article_KeyWords_Relation')
begin
create table [AC_Article_KeyWords_Relation]
(
KeyWords varchar(100),
ArticleId int
)
end
--从AC_Article_KeyWords_Relation表中查询数据
--根据ArticleId(传入的)查询出根据关键词的数量进行倒序排序, 文章关联的前十个关键词,
--传入13113得到的结果应该是
联合
联合重工
重工
insert into AC_Article_KeyWords_Relationselect '联合',13113 union
select '重工',13113 union
select '联合重工',13113 union
select '联合',13084 union
select '联合',12345 union
select '联合重工',8888 if not exists(select * from sysobjects where name='AC_Article')
begin
create table [AC_Article_KeyWords_Relation]
(
ArticleId int,
IsEnable bit,
IsPubish bit
)
endinsert into AC_Article
select 8888,0,1 union
select 12345,1,1 union
select 13084,1,0 union
select 13113,1,1
select top 10 aakr.KeyWords
from [AC_Article_KeyWords_Relation] as aakr,[AC_Article] as aa
where aakr.articleId=aa.ArticleId and aakr.keyWords in (select KeyWords from AC_Article_keyWords_Relation where ArticleId=13113)
group by aakr.KeyWords
order by count(aakr.articleId) desc,aakr.KeyWords desc
感觉有点长,并且不知道是否正确,执行效率是否高
select top 10 aakr.KeyWords
from [AC_Article_KeyWords_Relation] as aakr right join [AC_Article] as aa
on aakr.articleId=aa.ArticleId and aakr.ArticleId=13113
select top 10 aakr.KeyWords
from [AC_Article] as aa left join [AC_Article_KeyWords_Relation] as aakr
on aakr.articleId=aa.ArticleId and aa.ArticleId=13113
为什么我查询出来的结果不正确啊?如果是right join 就是10 NULL
如果是left join 就是依次查询出来,并没有根据关键词对应的总数量进行排序
from [AC_Article_KeyWords_Relation] as aakr,[AC_Article] as aa
where aakr.articleId=aa.ArticleId and aakr.keyWords in (select KeyWords from AC_Article_keyWords_Relation where ArticleId=13113)
group by aakr.KeyWords
order by count(aakr.articleId) desc,aakr.KeyWords desc
select aakr.KeyWords,count(aakr.articleId) as ArticleCount
from [AC_Article_KeyWords_Relation] as aakr left join [AC_Article] as aa
where aakr.articleId=aa.ArticleId and aakr.ArticleId=13113
group by aakr.KeyWords
order by count(aakr.articleId) desc,aakr.KeyWords desc
关键字 'where' 附近有语法错误。
--不好意思,打错了
select aakr.KeyWords,count(aakr.articleId) as ArticleCount
from [AC_Article_KeyWords_Relation] as aakr left join [AC_Article] as aa
on aakr.articleId=aa.ArticleId
where aakr.ArticleId=13113
group by aakr.KeyWords
order by count(aakr.articleId) desc,aakr.KeyWords desc
select aakr.KeyWords,count(aa.articleId) as ArticleCount
from [AC_Article_KeyWords_Relation] as aakr left join [AC_Article] as aa
on aakr.ArticleId=aa.ArticleId
where aakr.ArticleId=13113
group by aakr.KeyWords
order by count(aa.ArticleId) desc,aakr.KeyWords desc --count里用[AC_Article]表的ArticleId来算个数!
/**
*这样子查出来的count肯定是1了,表里又没记录某个关键词在文章里出现的次数,只是记录了在哪个文章里出现了,当然这个关键词和特定
*的文章ID只有一条记录了。
*/
select KeyWords from AC_Article_KeyWords_Relation where ArticleId