在MySQL中使用EXISTS进行查询的两个条件
在MySQL中使用EXISTS进行查询的两个条件
提问于 2016-10-03 03:56:21
我正在尝试获得满足这两个条件和存在条件的作者。但是，当子查询实际工作时，我会一直获取author表中的所有条目。有人能告诉我我的查询到底出了什么问题吗？我目前正在计算条目的数量。
SELECT COUNT(*)
FROM author
WHERE
EXISTS (SELECT A.author_id, A.author_name
FROM author AS A
INNER JOIN author_publication AS AP ON A.author_id = AP.author_id
INNER JOIN publication AS P ON AP.pub_id = P.pub_id
WHERE P.pub_key LIKE '%/pvldb/%'
GROUP BY A.author_id
HAVING COUNT(*)>=10)
AND
EXISTS (SELECT A.author_id, A.author_name
FROM author AS A
INNER JOIN author_publication AS AP ON A.author_id = AP.author_id
INNER JOIN publication AS P ON AP.pub_id = P.pub_id
WHERE P.pub_key LIKE '%/sigmod/%'
GROUP BY A.author_id
HAVING COUNT(*)>=10)
;回答 2
Stack Overflow用户
发布于 2016-10-03 04:09:14
尝试：
SELECT COUNT(A.author_id)
FROM
author AS A
INNER JOIN author_publication AS AP ON A.author_id = AP.author_id
INNER JOIN publication AS P ON AP.pub_id = P.pub_id
WHERE
P.pub_key LIKE '%/pvldb/%' OR P.pub_key LIKE '%/sigmod/%'
GROUP BY A.author_id
HAVING COUNT(A.author_id) >= 10;Stack Overflow用户
发布于 2016-10-03 04:22:23
您只需要将子查询与主查询关联起来：
SELECT COUNT(*)
FROM author as AA
WHERE
EXISTS (SELECT A.author_id, A.author_name
FROM author AS A
INNER JOIN author_publication AS AP ON A.author_id = AP.author_id
INNER JOIN publication AS P ON AP.pub_id = P.pub_id
WHERE P.pub_key LIKE '%/pvldb/%'
AND AA.author_id=A.author_id
GROUP BY A.author_id
HAVING COUNT(*)>=10)
AND
EXISTS (SELECT A.author_id, A.author_name
FROM author AS A
INNER JOIN author_publication AS AP ON A.author_id = AP.author_id
INNER JOIN publication AS P ON AP.pub_id = P.pub_id
WHERE P.pub_key LIKE '%/sigmod/%'
AND AA.author_id=A.author_id
GROUP BY A.author_id
HAVING COUNT(*)>=10)
;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39821076
复制相关文章
相似问题