select *
from tb a
where a.Keyword in
(
select
Keyword
from tb
group by Keyword
having count(Keyword) =5
)
from tb a
where a.Keyword in
(
select
Keyword
from tb
group by Keyword
having count(Keyword) =5
)
调试欢乐多
from tablea
where Keyword in (select Keyword from tablea where ArticleID=@ArticleID )
group by ArticleID
having count(ArticleID)>=5
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
ArticleID
from @t
group by ArticleID,Keyword
having count(*) = 5/**
ArticleID
----------
1
**/
select * from tb where Keyword in(select Keyword from tb group by Keyword having count(1) =5)
ArticleID Keyword
1 关键字1
1 关键字2
1 关键字3
1 关键字4
1 关键字5
2 关键字1
2 关键字2
2 关键字3
2 关键字4
2 关键字5
查处1和2这样的记录来
ArticleID Keyword
----------- --------------------
2 计划
2 空军
2 马公
2 美国
2 战机
DECLARE @TB TABLE(ArticleID INT, Keyword NVARCHAR(20))
INSERT @TB
SELECT 1, N'不堪一击' UNION ALL
SELECT 1, N'绝非' UNION ALL
SELECT 1, N'中国' UNION ALL
SELECT 2, N'计划' UNION ALL
SELECT 2, N'空军' UNION ALL
SELECT 2, N'马公' UNION ALL
SELECT 2, N'美国' UNION ALL
SELECT 2, N'战机' UNION ALL
SELECT 3, N'大盘' UNION ALL
SELECT 3, N'基金'SELECT * FROM @TB AS A WHERE (SELECT COUNT(*) FROM @TB WHERE ArticleID=A.ArticleID)=5
/*
ArticleID Keyword
----------- --------------------
2 计划
2 空军
2 马公
2 美国
2 战机
*/
INSERT @TB
SELECT 1, N'不堪一击' UNION ALL
SELECT 1, N'绝非' UNION ALL
SELECT 1, N'中国' UNION ALL
SELECT 2, N'计划' UNION ALL
SELECT 2, N'空军' UNION ALL
SELECT 2, N'马公' UNION ALL
SELECT 2, N'美国' UNION ALL
SELECT 2, N'战机' UNION ALL
SELECT 3, N'大盘' UNION ALL
SELECT 3, N'基金'
select * from @tb where ArticleID in(select ArticleID from @tb group by ArticleID having count(ArticleID) >=5)
/*
ArticleID Keyword
----------- --------------------
2 计划
2 空军
2 马公
2 美国
2 战机*/
DECLARE @TB TABLE(ArticleID INT, Keyword NVARCHAR(20))
INSERT @TB
SELECT 1, N'不堪一击' UNION ALL
SELECT 1, N'绝非' UNION ALL
SELECT 1, N'中国' UNION ALL
SELECT 2, N'计划' UNION ALL
SELECT 2, N'空军' UNION ALL
SELECT 2, N'马公' UNION ALL
SELECT 2, N'美国' UNION ALL
SELECT 2, N'战机' UNION ALL
SELECT 3, N'大盘' UNION ALL
SELECT 3, N'基金'SELECT *
FROM @TB AS A
WHERE A.ArticleID IN
(SELECT ArticleID FROM @TB GROUP BY ArticleID HAVING COUNT(ArticleID) = 5)
结果:
2 计划
2 空军
2 马公
2 美国
2 战机
select ArticleID
from tablea
where Keyword in (select Keyword from tablea where ArticleID=1)
group by ArticleID
having count(ArticleID)>=5
查出2来,呵呵
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)
*/
结果是按article大小排列,把满足要求的文章一对一对的列出来。
消息 512,级别 16,状态 1,第 1 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
select distinct ArticleID from @t where Keyword=(select Keyword from @t group by Keyword having count(*)=5)
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,'基金'
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
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 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