17,377
社区成员
发帖
与我相关
我的任务
分享
SELECT KK.WORD
FROM KEYWORD KK
WHERE EXISTS (SELECT KC.KID
FROM CLASS CC, KEYWORD_CLASS KC
WHERE CC.CID = KC.CID
AND KC.KID = KK.KID
AND CC.NAME IN ('门户网站'))
AND EXISTS (SELECT KC.KID
FROM CLASS CC, KEYWORD_CLASS KC
WHERE CC.CID = KC.CID
AND KC.KID = KK.KID
AND CC.NAME IN ('搜索引擎'))
AND NOT EXISTS (SELECT KC.KID
FROM CLASS CC, KEYWORD_CLASS KC
WHERE CC.CID = KC.CID
AND KC.KID = KK.KID
AND CC.CID NOT IN (SELECT CID FROM CLASS WHERE CC.NAME IN ('门户网站', '搜索引擎'));
SELECT KK.WORD
FROM KEYWORD KK
WHERE EXISTS (SELECT KC.KID
FROM CLASS CC, KEYWORD_CLASS KC
WHERE CC.CID = KC.CID
AND KC.KID = KK.KID
group by kc.kid
having sum(decode(cc.name,'门户网站',0,'搜索引擎',0,1))=0
and sum(decode(cc.name,'门户网站',1,'搜索引擎',1,0))=2
)
WITH CC AS(
select CID FROM CLASS CC
WHERE NAME IN ('门户网站', '搜索引擎')
GROUP BY CID HAVING COUNT(DISTINCT NAME)=2
)
SELECT KK.WORD
FROM KEYWORD KK
WHERE EXISTS(SELECT 1 FROM CC,KEYWORD_CLASS KC
WHERE CC.CID = KC.CID
AND KC.KID = KK.KID)