22,209
社区成员
发帖
与我相关
我的任务
分享
create table tb(ArticleID int, Keyword nvarchar(100))
insert tb select
1 , '关键字1' union select
1 , '关键字2' union select
1 , '关键字3' union select
1 , '关键字4' union select
1 , '关键字5' union select
2 , '关键字1' union select
2 ,'关键字2' union select
2 ,'关键字3' union select
2 ,'关键字4' union select
2 ,'关键字5' union select
3 , '关键字1' union select
3 ,'关键字2' union select
3 ,'关键字3' union select
3 ,'关键字4' union select
3 ,'关键字5' union select
4 , '关键字1' union select
4 ,'关键字2' union select
4 ,'关键字3' union select
5 , '关键字1' union select
5 ,'关键字2'
select a.ArticleID,SimilarArticleID=b.ArticleID
from (select distinct ArticleID from tb) a join (select distinct ArticleID from tb) b
on a.ArticleID<b.ArticleID
and (select count(distinct Keyword) from tb where ArticleID=b.ArticleID and Keyword in(select Keyword from tb where ArticleID=a.ArticleID))=5
/*
ArticleID SimilarArticleID
----------- ----------------
1 2
1 3
2 3
*/
drop table tb
select distinct a.ArticleID as one, b.ArticleID as two
from article a inner join article b on a.keyword=b.keyword and a.articleid<>b.articleid
group by a.ArticleID,b.ArticleID
having count(b.ArticleID)>4
create table tb(ArticleID int, Keyword nvarchar(100))
insert tb select
1 , '关键字1' union select
1 , '关键字2' union select
1 , '关键字3' union select
1 , '关键字4' union select
1 , '关键字5' union select
2 , '关键字1' union select
2 ,'关键字2' union select
2 ,'关键字3' union select
2 ,'关键字4' union select
2 ,'关键字5' union select
3 , '关键字1' union select
3 ,'关键字2' union select
3 ,'关键字3' union select
3 ,'关键字4' union select
3 ,'关键字5' union select
4 , '关键字1' union select
4 ,'关键字2' union select
4 ,'关键字3' union select
5 , '关键字1' union select
5 ,'关键字2'
select distinct a.ArticleID,SimilarArticleID=b.ArticleID
from tb a,tb b
where a.ArticleID<b.ArticleID
and (select count(distinct Keyword) from tb where ArticleID=b.ArticleID and Keyword in(select Keyword from tb where ArticleID=a.ArticleID))=5
/*
ArticleID SimilarArticleID
----------- ----------------
1 2
1 3
2 3
(3 row(s) affected)
(1 row(s) affected)
*/
select * from tb
drop table tb
declare @t table
(
ArticleID int,
Keyword varchar(20)
)
insert @t select 1,'不堪一击'
insert @t select 1,'绝非'
insert @t select 1,'不堪一击'
insert @t select 1,'不堪一击'
insert @t select 1,'不堪一击'
insert @t select 1,'不堪一击'
insert @t select 2,'空军'
insert @t select 2,'空军'
insert @t select 2,'马公'
insert @t select 2,'美国'
insert @t select 2,'战机'
insert @t select 3,'大盘'
insert @t select 3,'基金'
select distinct ArticleID from @t where Keyword=(select Keyword from @t group by Keyword having count(*)=5)
create table tb(ArticleID int, Keyword nvarchar(100))
insert tb select
1 , '关键字1' union select
1 , '关键字2' union select
1 , '关键字3' union select
1 , '关键字4' union select
1 , '关键字5' union select
2 , '关键字1' union select
2 ,'关键字2' union select
2 ,'关键字3' union select
2 ,'关键字4' union select
2 ,'关键字5' union select
3 , '关键字1' union select
3 ,'关键字2' union select
3 ,'关键字3'
select distinct ArticleID,SimilarArticleID from
(
select ArticleID,SimilarArticleID=(select ArticleID from tb t1 where ArticleID>t.ArticleID and Keyword in (select Keyword from tb where ArticleID=t1.ArticleID) group by ArticleID having count(*)=5)
from tb t
) t2
where SimilarArticleID is not null
/*
ArticleID SimilarArticleID
----------- ----------------
1 2
(1 row(s) affected)
*/